Archive

Archive for the ‘CONVERT’ Category

Formating Date and Time

May 29, 2010 1 comment

We synchronize GIS data differentially using a proprietary method. As part of the process a date-time parameter is passed and the system updates the GIS accordingly given the time stamp value. Since this is a differential sync only the changes that occurred within the range are applied to the subscriber. A specific format is required but none of the following formats in the example below meet the criteria exactly.

Format 101 addresses the needed date style (MM/DD/YYYY) but there really is not a time format that fits my exact needs. This is where I improvise a bit. Since I pull values from a table that have a “datetime” data type I will break the date and time into two pieces and address each as needed. In this case I only need to worry about the time.

SELECT CONVERT(varchar, GETDATE(), 100) 'Mon DD YYYY hh:miAM'
	  ,CONVERT(varchar, GETDATE(), 101) 'MM/DD/YYYY'
	  ,CONVERT(varchar, GETDATE(), 102) 'YYYY.MM.DD'
	  ,CONVERT(varchar, GETDATE(), 103) 'DD/MM/YYYY'
	  ,CONVERT(varchar, GETDATE(), 104) 'DD.MM.YYYY'
	  ,CONVERT(varchar, GETDATE(), 105) 'DD-MM-YYYY'
	  ,CONVERT(varchar, GETDATE(), 106) 'DD Mon YYYY'
	  ,CONVERT(varchar, GETDATE(), 107) 'Mon DD, YYYY'
	  ,CONVERT(varchar, GETDATE(), 108) 'hh:mi:ss' -- 24HR
	  ,CONVERT(varchar, GETDATE(), 109) 'Mon DD YYYY hh:mi:ss:mmmAM'
	  ,CONVERT(varchar, GETDATE(), 110) 'MM-DD-YYYY'
	  ,CONVERT(varchar, GETDATE(), 111) 'YYYY/MM/DD'
	  ,CONVERT(varchar, GETDATE(), 112) 'YYYYMMDD'
	  ,CONVERT(varchar, GETDATE(), 113) 'DD Mon YYYY hh:mi:ss:mmm' --24HR
	  ,CONVERT(varchar, GETDATE(), 114) 'hh:mi:ss.mmm' -- 24HR
	  ,CONVERT(varchar, GETDATE(), 120) 'YYYY-MM-DD hh:mi:ss' --24HR
	  ,CONVERT(varchar, GETDATE(), 121) 'YYYY-MM-DD hh:mi:ss.mmm' --24HR
	  ,CONVERT(varchar, GETDATE(), 126) 'YYYY-MM-DDThh:mi:ss.mmm'
	  ,CONVERT(varchar, GETDATE(), 127) 'YYYY-MM-DDThh:mi:ss.mmmZ'
	  ,CONVERT(varchar, GETDATE(), 130) 'DD Mon YYYY hh:mi:ss:mmmAM'
	  ,CONVERT(varchar, GETDATE(), 131) 'DD/MM/YY hh:mi:ss:mmmAM'

At first I spotted format 108 which provided hh:mi:ss but it lacked the AM/PM designation but appending it would be simple. So here is what I did.

SELECT [ModifiedDate]
       ,CONVERT(varchar,ModifiedDate,101) 'Date Only'
       ,CONVERT(varchar,ModifiedDate,108) 'Time Only'
       ,CONVERT(varchar,ModifiedDate,101) + ' ' +
       CASE 
	     WHEN CONVERT(varchar(2),ModifiedDate,108) > 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' PM'
	     WHEN CONVERT(varchar(2),ModifiedDate,108) < 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' AM'					
       END AS 'New ModifiedDate'
FROM [AdventureWorks].[Person].[Address]
WHERE [AddressID] IN (34,35)

Note: I had to update AddressID 35 for illustration purposes.

Outcome
FirstAttempt

The end results are close but not exactly there yet. The parameter only accepts time in a 12 hour format not 24. So I turned to my old scripts to see if I have done this before and I hadn’t but I did use DATEPART in one of them. After jumping into BOL and reading up on DATEPART I found that I could break apart the time into smaller segments (hours, minutes, seconds, etc…) and format them individually. It would require more syntax but the end results would be exactly what I needed.

SELECT [ModifiedDate]
       ,CONVERT(varchar,ModifiedDate,101) 'Date Only'
       ,CONVERT(varchar,ModifiedDate,108) 'Time Only'
       ,CONVERT(varchar,ModifiedDate,101) + ' ' +
       CASE 
	     WHEN CONVERT(varchar(2),ModifiedDate,108) > 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' PM'
	     WHEN CONVERT(varchar(2),ModifiedDate,108) < 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' AM'					
       END AS 'New ModifiedDate'
	   ,DATEPART(HH,ModifiedDate) 'HH'
	   ,DATEPART(MI,ModifiedDate) 'MI'
	   ,DATEPART(SS,ModifiedDate) 'SS'
FROM [AdventureWorks].[Person].[Address]
WHERE [AddressID] IN (34,35)

Outcome
Attempt2

Now the problem is that the MI column only displays a single digit when the value is less than 10. This would be the same behavior for the HH and SS columns. So I modified AddressID 36 to illustrate this as well.

Outcome
Attempt3

This means I need to do two things: 1) change the time to return in a 12 hr format and 2) pad the HH, MI & SS columns with a leading zero. I’ll tackle item 2) first by adding a space left of number…

SELECT [ModifiedDate]
       ,CONVERT(varchar,ModifiedDate,101) 'Date Only'
       ,CONVERT(varchar,ModifiedDate,108) 'Time Only'
       ,CONVERT(varchar,ModifiedDate,101) + ' ' +
       CASE 
	     WHEN CONVERT(varchar(2),ModifiedDate,108) > 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' PM'
	     WHEN CONVERT(varchar(2),ModifiedDate,108) < 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' AM'					
       END AS 'New ModifiedDate'
	   ,str(DATEPART(HH,ModifiedDate),2) 'HH'
	   ,str(DATEPART(MI,ModifiedDate),2) 'MI'
	   ,str(DATEPART(SS,ModifiedDate),2) 'SS'
FROM [AdventureWorks].[Person].[Address]
WHERE [AddressID] IN (34,35,36)

Outcome
Attempt4

Now I need to replace the space with a zero…

SELECT [ModifiedDate]
       ,CONVERT(varchar,ModifiedDate,101) 'Date Only'
       ,CONVERT(varchar,ModifiedDate,108) 'Time Only'
       ,CONVERT(varchar,ModifiedDate,101) + ' ' +
       CASE 
	     WHEN CONVERT(varchar(2),ModifiedDate,108) > 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' PM'
	     WHEN CONVERT(varchar(2),ModifiedDate,108) < 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' AM'					
       END AS 'New ModifiedDate'
	   ,REPLACE(str(DATEPART(HH,ModifiedDate),2),SPACE(1),0) 'HH'
	   ,REPLACE(str(DATEPART(MI,ModifiedDate),2),SPACE(1),0) 'MI'
	   ,REPLACE(str(DATEPART(SS,ModifiedDate),2),SPACE(1),0) 'SS'
FROM [AdventureWorks].[Person].[Address]
WHERE [AddressID] IN (34,35,36)

Outcome
Attempt5

Now we are looking better, but I still need to convert the 24 hr time to 12 hr time. So I’ll take the HH column and subtract 12 from it for any value greater than 12. If it is less than 12 then display as normal.

SELECT [ModifiedDate]
       ,CONVERT(varchar,ModifiedDate,101) 'Date Only'
       ,CONVERT(varchar,ModifiedDate,108) 'Time Only'
       ,CONVERT(varchar,ModifiedDate,101) + ' ' +
       CASE 
	     WHEN CONVERT(varchar(2),ModifiedDate,108) > 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' PM'
	     WHEN CONVERT(varchar(2),ModifiedDate,108) < 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' AM'					
       END AS 'New ModifiedDate'
	   ,CASE 
	      WHEN (DATEPART(HH,ModifiedDate) > 12)
	      THEN REPLACE(str(DATEPART(HH,ModifiedDate)-12,2),SPACE(1),0)
	      ELSE REPLACE(str(DATEPART(HH,ModifiedDate),2),SPACE(1),0)
	   END AS 'HH'
	   ,REPLACE(str(DATEPART(MI,ModifiedDate),2),SPACE(1),0) 'MI'
	   ,REPLACE(str(DATEPART(SS,ModifiedDate),2),SPACE(1),0) 'SS'
FROM [AdventureWorks].[Person].[Address]
WHERE [AddressID] IN (34,35,36)

Outcome
Attempt6

Zinga! Now the only thing left to do is to concatenate the Date Only field with the HH, MI and SS fields and append the AM/PM designation.

SELECT [ModifiedDate]
       ,CONVERT(varchar,ModifiedDate,108) 'Time Only'
       ,CONVERT(varchar,ModifiedDate,101) + ' ' +
       CASE 
	     WHEN CONVERT(varchar(2),ModifiedDate,108) > 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' PM'
	     WHEN CONVERT(varchar(2),ModifiedDate,108) < 12
	     THEN CONVERT(varchar,ModifiedDate,108)+ ' AM'					
       END AS 'New ModifiedDate'
       ,CONVERT(varchar,ModifiedDate,101) + ' ' +
	   CASE 
	      WHEN (DATEPART(HH,ModifiedDate) > 12)
	      THEN REPLACE(str(DATEPART(HH,ModifiedDate)-12,2),SPACE(1),0)
	      ELSE REPLACE(str(DATEPART(HH,ModifiedDate),2),SPACE(1),0)
	   END + ':' +
	   REPLACE(str(DATEPART(MI,ModifiedDate),2),SPACE(1),0) + ':' +
	   REPLACE(str(DATEPART(SS,ModifiedDate),2),SPACE(1),0) +
	   CASE 
	      WHEN (DATEPART(HH,ModifiedDate) < 12)
	      THEN ' AM'
	      ELSE ' PM'
	   END AS 'Usable Date'
FROM [AdventureWorks].[Person].[Address]
WHERE [AddressID] IN (34,35,36)

Final Outcome
FinalAttempt

Categories: CONVERT, SQL Server, TSQL Tags: , , , , , , , , ,
Follow

Get every new post delivered to your Inbox.