SQL Server Date & Datepart formats
Here is a handy cheat sheet for dates in SQL Server:
-- DATEPART SAMPLES select 'Year: ' + CONVERT(NVARCHAR(4),datepart(year, GETDATE())) select 'Quarter: ' + CONVERT(CHAR(1),datepart(quarter , GETDATE())) select 'Month: ' + CONVERT(NVARCHAR(2),datepart(month, GETDATE())) select 'Day of Year: ' + CONVERT(NVARCHAR(3),datepart(dayofyear, GETDATE())) select 'Day of Month: ' + CONVERT(NVARCHAR(2),datepart(day, GETDATE())) select 'Week: ' + CONVERT(NVARCHAR(2),datepart(week, GETDATE())) select 'Weekday: ' + CONVERT(CHAR(1),datepart(weekday, GETDATE())) select 'Hour: ' + CONVERT(NVARCHAR(2),datepart(hour, GETDATE())) select 'Minute: ' + CONVERT(NVARCHAR(2),datepart(minute, GETDATE())) select 'Second: ' + CONVERT(NVARCHAR(2),datepart(second, GETDATE())) select 'Millisecond: ' + CONVERT(NVARCHAR(3),datepart(millisecond, GETDATE())) select 'Microsecond: ' + CONVERT(NVARCHAR(6),datepart(microsecond, GETDATE())) select 'Nanosecond: ' + CONVERT(NVARCHAR(9),datepart(nanosecond, GETDATE())) -- VARIOUS DATE FORMATS SELECT CONVERT(CHAR(19), GETDATE(), 100) -- Jun 12 2009 8:49AM SELECT CONVERT(CHAR(25), GETDATE(), 101) -- 06/12/2009 SELECT CONVERT(CHAR(25), GETDATE(), 102) -- 2009.06.12 SELECT CONVERT(CHAR(25), GETDATE(), 103) -- 12/06/2009 SELECT CONVERT(CHAR(25), GETDATE(), 107) -- Jun 12, 2009 SELECT CONVERT(CHAR(25), GETDATE(), 108) -- 08:51:49 SELECT CONVERT(CHAR(25), GETDATE(), 110) -- 06-12-2009 SELECT CONVERT(CHAR(25), GETDATE(), 111) -- 2009/06/12 SELECT CONVERT(CHAR(25), GETDATE(), 112) -- 20090612 SELECT CONVERT(CHAR(25), GETDATE(), 114) -- 08:53:06:000 SELECT CONVERT(CHAR(25), GETDATE(), 120) -- 2009-06-12 08:55:28 SELECT CONVERT(CHAR(25), GETDATE(), 121) -- 2010-09-24 09:16:35.520
