Date Formats in Sql Server

--Date--
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
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(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)
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
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)
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
--Date
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 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]
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]
--Date
SELECT DATEADD()
SELECT DATEADD(datepart, 5, GETDATE())
SELECT GETDATE()
SELECT DATEPART()
SELECT DATEDIFF()
SELECT DATENAME()
SELECT DAY()
SELECT MONTH()
SELECT YEAR()
SELECT ASCII()
SELECT CHAR() 
SELECT NCHAR()
SELECT DIFFERENCE()
SELECT LEFT()
SELECT RIGHT()
SELECT LTRIM()
SELECT RTRIM()
SELECT REPLACE()
SELECT QUOTENAME()
SELECT REVERSE()
SELECT CHARINDEX()
SELECT PATINDEX()
SELECT LEN()
SELECT STUFF
SELECT SUBSTRING
SELECT LOWER
SELECT UPPER
SELECT DATEADD(day, 10,'2000-01-05 00:05:00.000');
SELECT DATEDIFF(day, '2000-01-10','2000-01-05');
SELECT DATEPART(day, '2000-02-10');


Tags:
Permalink | Comments (0) | Post RSSRSS comment feed