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
Technology Tips and News
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
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
I am not using any kind of UI.
I need that in SSIS
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")
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.