Massive SQL Date Time Cheat Sheet
Anyone who has been involved in SQL will undoubtedly tell you wargames they played when they had to manipulate date time data via SQL.
Here are code snippets to multiple Date and Time calculations most, if not all, MS-SQL developers have needed at some stage with the following, commonly used MS SQL date functions:
Enjoy!
Months
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- First day of previous month SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) -- Last Day of previous month SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) -- First day of this month SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) -- Last day of this month SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) -- First day of next month SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) -- Last day of next month SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, -1) |
Quarters
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
-- First day of previous quarter SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) -1, 0) -- Last day of previous quarter SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), -1) -- First day of this quarter SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) -- Last day of this quarter SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, -1) -- First day of next quarter SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, 0) -- Last day of next quarter SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 2, -1) -- First day of 1st quarter of previous year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) -- Last day of 1st quarter of previous year SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- First day of 2nd quarter of previous year SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- Last day of 2nd quarter of previous year SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- First day of 3rd quarter of previous year SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- Last day of 3rd quarter of previous year SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- First day of 4th quarter of previous year SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- Last day of 4th quarter of previous year SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- First day of 1st quarter of current year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) -- Last day of 1st quarter of current year SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) -- First day of 2nd quarter of current year SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- Last day of 2nd quarter of current year SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) -- First day of 3rd quarter of current year SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- Last day of 3rd quarter of current year SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) -- First day of 4th quarter of current year SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- Last day of 4th quarter of current year SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) |
Years
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- First day of previous year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -1, 0) -- Last day of previous year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1) -- First day of this year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) -- Last day of this year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1) -- First day of next year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) -- Last day of next year SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, -1) |
Half Years
|
1 2 3 4 5 6 7 8 |
-- First day of second half of previous year SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -1, 0)) -- First day of second half of this year SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of second half of next year SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) |
Miscellaneous
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
-- Now SELECT GETDATE() -- Yesterday SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) -- Today SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) -- Tomorrow SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1) -- Day of month SELECT DAY(GETDATE()) -- 30 days ago SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -30) -- 90 days ago SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -90) -- 1 month ago since last midnight SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, DAY(GETDATE())-1) -- 3 months ago since last midnight SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, DAY(GETDATE())-1) -- 6 months ago since last midnight SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, DAY(GETDATE())-1) -- 12 months ago since last midnight SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, DAY(GETDATE())-1) |
Others
Have any other date/time codes worthy of being added to this Massive SQL Date Time Cheat Sheet?
If so, leave them in the comments below!
