As I’ve mentioned before, I think I’m the most avid reader of my blog. I use it to document my work so that I don’t have to constantly re-invent the wheel. In this case, I find myself repeatedly searching for these scripts, often to populate SQL Reporting Services parameter defaults, so I thought I would share them with my readers as well.
-- Midnight of Current Day SELECT dateadd (dd, 0, datediff (dd, 0, Getdate ())) -- Midnight of last Friday (or whichever day). /*The day of the week of date entered determines the result. Pick a Friday, any Friday, and you will get last Friday. Replace the - 0 with any increment of 7 to get previous weeks.*/ SELECT DATEADD (day,((DATEDIFF (day, '20120203', CURRENT_TIMESTAMP) / 7) * 7) - 0,'20120203') --Day Number of Current year -- Removed because the query was faulty. See comments. --First Day of Month SELECT DATEADD (mm, DATEDIFF (mm, 0, GetDate ()), 0) --Last Day of Month SELECT DATEADD (s, -1, DATEADD (mm, DATEDIFF (m, 0, GETDATE ()) + 1, 0)) --First Day of Current Year SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()), 0) --First Day of Previous Years --Substitute '-1' with whichever number of years to reverse SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) - 1, 0) --First Day of Next Year SELECT DATEADD (yy, DATEDIFF (yy, 0, getdate ()) + 1, 0) --Past 30 days. SELECT DATEADD (dd, 0, DATEDIFF (dd, 30, GETDATE ())) --Add in beginning and end of prior quarter. SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()), 0) --First Day of Prior Quarter SELECT DATEADD (qq, DATEDIFF (qq, 0, getdate ()) - 1, 0)
I’m not taking credit for these scripts as I’ve
stolen borrowed these scripts from various places, most recently this excellent post.
Do you have any queries that you’d like to add?