Posts
33
Comments
186
Trackbacks
0
Thursday, June 9, 2011
Date/Time formats in SQL Server

In SQL server a column with datatype of DateTime stores the time to the millisecond, but many times you just like to show the date and time only upto the second.

Although there are many ways to achieve this, I'm just going to show the couple of possible ways -

1.)

SELECT CONVERT(VARCHAR,GETDATE(),120)

This would yield the current date time in YYYY-MM-DD HH:MM:SS (ofcourse depends on your regional setting)

2.) You can get the millisecond part from the getdate() function and subtract it with the getdate() to leave you with time only upto second.

SELECT DATEADD(ms, -DATEPART(ms,GetDate()),GetDate())

As I mentioned earlier, these are just some of the possible ways to acheive the desired result.

The post www.sql-server-helper.com/tips/date-formats.aspx has some very good collection to acheive various formats in date/time in SQL.

Hope this helps!

 

Posted On Thursday, June 9, 2011 12:21 PM | Comments (2)