Saturday, February 6, 2010

Time alone in SQL server

Most the times we need to format the Datetime column while displaying the record,
although formatting date in Backend is not a good practice, because it changes the datatype of the Date to varchar

We can split datetime in number of formats.
Microsoft has listed the possible format to get date alone in the below link..

http://msdn.microsoft.com/en-us/library/ms187928.aspx

Let me go with time part alone,
Below I have provided 4 different time formats.

This codeworks fine in SQL server 2005

Here We GO...!!!

To get the time part alone from given date

HH:MM:SS
SELECT CONVERT(VARCHAR, GETDATE(), 108)
Result : 20:43:28

HH:MM:SS.MS
SELECT RIGHT(CONVERT(VARCHAR, GETDATE(), 121) ,12)
Result : 20:43:28.327

HH:MM(AM/PM)
SELECT RIGHT(CONVERT(VARCHAR(20), GETDATE(), 100),6)
Result : 8:43PM

HH:MM:SS:MS(AM/PM)
SELECT RIGHT(CONVERT(VARCHAR(30), GETDATE(), 109),13)
Result : 8:43:28:327PM

No comments:

Post a Comment