Sunday, January 9, 2011

Different DATETIME formats.

DBAs are always looking for different ways to return the date.  Strip off the time, present the actual day or month name, include 'AM' or 'PM', use different separators... and on and on.  I've listed a few of my date formatting examples here.  Take a look, and let me know if you need something more.  Also, please remember that formatting or presentation of the data really should be done on the front end.  This stuff may be pretty, but it's not actually free.  Please keep in mind, there is overhead to formatting the date data like this.


SELECT CONVERT(VARCHAR(10),GETDATE(),1)   [MM/DD/YY]
SELECT CONVERT(VARCHAR(10),GETDATE(),4)   [MM.DD.YY]
SELECT CONVERT(VARCHAR(12),GETDATE(),111) [YYYY/MM/DD]
SELECT CONVERT(VARCHAR(12),GETDATE(),101) [MM/DD/YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),102) [YYYY.MM.DD]
SELECT CONVERT(VARCHAR(12),GETDATE(),103) [DD/MM/YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),104) [DD.MM.YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),105) [DD-MM-YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),113) [DD Mon YYYY]
SELECT CONVERT(VARCHAR(12),GETDATE(),108) [hh:mm:ss]
SELECT CONVERT(VARCHAR(12),GETDATE(),110) [MM-DD-YYYY]
SELECT SUBSTRING(CONVERT(VARCHAR(11),DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),-1),113),4,8) [Mon YYYY]
SELECT LEFT(GETDATE(),11) [Mon D, YYYY]
SELECT CONVERT(CHAR(8),GETDATE(),112) [YYYYMMDD]
SELECT LEFT(CONVERT(CHAR(10),DATEADD(dd,-0,GETDATE()),101),5) [MM/DD]
SELECT CONVERT(DATETIME,CONVERT(CHAR(10),GETDATE(),120),120) [YYYY-MM-DD hh:mm:ss:ms ]
SELECT CONVERT(VARCHAR(10),GETDATE(),101)+ ''+SUBSTRING(CONVERT(VARCHAR(20),GETDATE(),100),13,7) [MM/DD/YYYY hh:mmPM]
SELECT DATENAME(MONTH,GETDATE()) AS [Month] 
SELECT DATENAME(m, DATEADD ( m , -1, GETDATE() )) [Last Month]
SELECT LEFT(getdate()-0, 11) [Today], LEFT(DATEADD(dy, 1, GETDATE()),11) [Tomorrow]
SELECT CONVERT(CHAR(5),GETDATE(),8) [hh:mm]
SELECT SUBSTRING(CONVERT(VARCHAR,GETDATE(),100),13,9) [hh:mmPM]
SELECT CONVERT(VARCHAR(100),GETDATE(),114) [hh:mm:ss:ms ]

No comments:

Post a Comment