Sql Server Datetime Tips and Tricks
Months
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0) -- First day of previous month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1) -- Last Day of previous month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) -- First day of this month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) -- Last day of this month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0) -- First day of next month
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, -1) -- Last day of next month
Quarters
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) -1, 0) -- First day of previous quarter
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), -1) -- Last day of previous quarter
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()), 0) -- First day of this quarter
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, -1) -- Last day of this quarter
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 1, 0) -- First day of next quarter
SELECT DATEADD(QUARTER, DATEDIFF(QUARTER, 0, GETDATE()) + 2, -1) -- Last day of next quarter
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) -- First day of 1st quarter of previous year
SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- Last day of 1st quarter of previous year
SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- First day of 2nd quarter of previous year
SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- Last day of 2nd quarter of previous year
SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- First day of 3rd quarter of previous year
SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- Last day of 3rd quarter of previous year
SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0)) -- First day of 4th quarter of previous year
SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, -1)) -- Last day of 4th quarter of previous year
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) -- First day of 1st quarter of current year
SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) -- Last day of 1st quarter of current year
SELECT DATEADD(QUARTER, 1, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of 2nd quarter of current year
SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) -- Last day of 2nd quarter of current year
SELECT DATEADD(QUARTER, 2, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of 3rd quarter of current year
SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) -- Last day of 3rd quarter of current year
SELECT DATEADD(QUARTER, 3, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of 4th quarter of current year
SELECT DATEADD(QUARTER, 4, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1)) -- Last day of 4th quarter of current year
Years
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -1, 0) -- First day of previous year
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), -1) -- Last day of previous year
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) -- First day of this year
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, -1) -- Last day of this year
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) -- First day of next year
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, -1) -- Last day of next year
Half Years
SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) -1, 0)) -- First day of second half of previous year
SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0)) -- First day of second half of this year
SELECT DATEADD(MONTH, 6, DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)) -- First day of second half of next year
Other
SELECT GETDATE() -- Now
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -1) -- Yesterday
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0) -- Today
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 1) -- Tomorrow
SELECT DAY(GETDATE()) -- Day of month
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -30) -- 30 days ago
SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), -90) -- 90 days ago
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, DAY(GETDATE())-1) -- 1 months ago since last midnight
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 3, DAY(GETDATE())-1) -- 3 months ago since last midnight
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 6, DAY(GETDATE())-1) -- 6 months ago since last midnight
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 12, DAY(GETDATE())-1) -- 12 months ago since last midnight
Written by Rick Pearson
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Sql
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#