Date Formats

Posted: December 30, 2009 in SQL Server 2005

You can format a date using the following format codes:

SELECT CONVERT(VARCHAR(20), GETDATE(), 100) AS [Mon DD YYYY HH:MIAM (or PM)];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 1) AS [MM/DD/YY];
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 2) AS [YY.MM.DD];
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 3) AS [DD/MM/YY];
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 4) AS [DD.MM.YY];
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 5) AS [DD-MM-YY];
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY];
SELECT CONVERT(VARCHAR(9),  GETDATE(), 6) AS [DD MON YY];
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY];
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY];
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 108) AS [HH:MM:SS];
SELECT CONVERT(VARCHAR(26), GETDATE(), 109) AS [Mon DD YYYY HH:MI:SS:MMMAM (or PM)];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 10) AS [MM-DD-YY];
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 11) AS [YY/MM/DD];
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD];
SELECT CONVERT(VARCHAR(6),  GETDATE(), 12) AS [YYMMDD];
SELECT CONVERT(VARCHAR(8),  GETDATE(), 112) AS [YYYYMMDD];
SELECT CONVERT(VARCHAR(24), GETDATE(), 113) AS [DD Mon YYYY HH:MM:SS:MMM(24h)];
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)];
SELECT CONVERT(VARCHAR(19), GETDATE(), 120) AS [YYYY-MM-DD HH:MI:SS(24h)];
SELECT CONVERT(VARCHAR(23), GETDATE(), 121) AS [YYYY-MM-DD HH:MI:SS.MMM(24h)];
SELECT CONVERT(VARCHAR(23), GETDATE(), 126) AS [YYYY-MM-DDTHH:MM:SS:MMM];
SELECT CONVERT(VARCHAR(26), GETDATE(), 130) AS [DD Mon YYYY HH:MI:SS:MMMAM];
SELECT CONVERT(VARCHAR(25), GETDATE(), 131) AS [DD/MM/YYYY HH:MI:SS:MMMAM];
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8 ) AS [YY-MM-DD];
SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD];
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD];
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD];
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY];
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY];
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY];
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM];
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM];
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY];
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8 ) AS [Mon YYYY];
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY];
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month];
SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD];
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY];
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY];
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY];
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM];
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM];
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM];
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY];
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY];
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY];
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY];
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY];
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY];
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY];
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY];
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY];
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY];
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s