Date Time Format

Hi 

I have datetime column in my table. I need to convert to '06-APR-15 09.55.29.390000000 PM' this format.

Hour must be between 1 and 12.AM or PM should be present along with the time.

can any one please help.

Thanks,

Rahul 

April 30th, 2015 5:29pm

Formatting should be done in your presentation layer, not in your database.

06-APR-15 09.55.29.390000000 PM this is not a datetime, it's a string..

 DECLARE @table TABLE (datetime DATETIME)
 INSERT INTO @table (datetime) VALUES (CURRENT_TIMESTAMP)

 SELECT RIGHT('0'+CAST(DATEPART(DAY,datetime) AS VARCHAR),2)+'-'+
        UPPER(LEFT(DATENAME(MONTH,datetime),3))+'-'+
		RIGHT(CAST(DATEPART(YEAR,datetime) AS VARCHAR),2)+' '+
		RIGHT('0'+CAST(DATEPART(HOUR,datetime) - CASE WHEN DATEPART(HOUR,datetime) > 11 THEN 12 ELSE 0 END  AS VARCHAR),2)+'.'+
		RIGHT('0'+CAST(DATEPART(MINUTE,datetime) AS VARCHAR),2)+'.'+
		RIGHT('0'+CAST(DATEPART(SECOND,datetime) AS VARCHAR),2)+'.'+
		CAST(DATEPART(MILLISECOND,datetime) AS VARCHAR)+'000000 '+
		CASE WHEN DATEPART(HOUR,datetime) > 11 THEN 'PM' ELSE 'AM' END
   FROM @table
Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 5:30pm

I am not using any kind of UI.

I need that in SSIS

April 30th, 2015 5:54pm

Then you posted to the wrong forum.

Try this, it may need some tweaking.

RIGHT("0"+(DT_WSTR,2)DATEPART(DAY,datetime),2)+"-"+
        UPPER(LEFT(DATENAME(MONTH,datetime),3))+"-"+
		RIGHT(CAST(DATEPART(YEAR,datetime) AS VARCHAR),2)+" "+
		RIGHT("0"+(DT_WSTR,2)DATEPART(HOUR,datetime) - (DATEPART(HOUR,datetime) > 11 ? 12 : 0),2)+"."+
		RIGHT("0"+(DT_WSTR,2)DATEPART(MINUTE,datetime),2)+"."+
		RIGHT("0"+(DT_WSTR,2)DATEPART(SECOND,datetime),2)+"."+
		(DT_WSTR,3)DATEPART(MILLISECOND,datetime)+"000000 "+
		(DATEPART(HOUR,datetime) > 11 ? "PM" : "AM")

Free Windows Admin Tool Kit Click here and download it now
April 30th, 2015 6:16pm

In SQL 2012 and later, you can use the FORMAT function with a custom formatting string (same as those used in a .NET application):

SELECT UPPER(FORMAT(YourDateTimeColumn, 'dd-MMM-dd hh.mm.ss.fffffff tt'))

I agree with Patrick that the presentation layer is a better place for this task.

April 30th, 2015 8:53pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics