Reporting - Default dates

When writing SSRS reports, we always have the need to set default dates for reports.  Usually prior week, or prior month.  Sometimes they are current week to date, or current week to month.  Whatever your default reporting needs are, here is a list that should get you started.



DECLARE @Date datetime = GETDATE()

-- Start of Month
SELECT DATEADD(m, DATEDIFF(m, 0, @Date), 0) [Start of Month]
-- End of Month
SELECT DATEADD(d,-1,DATEADD(m,1,DATEADD(m, DATEDIFF(m, 0, @Date), 0))) [End of Month]
-- Start of Week
SELECT DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)) [Start of Week]
-- End of Week
SELECT DATEADD(d,-1,DATEADD(WEEK,1,DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0)))) [End of Week]

-- Start of Prior Month
SELECT DATEADD(m,-1,DATEADD(m, DATEDIFF(m, 0, @Date), 0)) [Start of Prior Month]
-- End of Prior Month
SELECT DATEADD(d,-1,DATEADD(m,-1,DATEADD(m,1,DATEADD(m, DATEDIFF(m, 0, @Date), 0)))) [End of Prior Month]
-- Start of Prior Week
SELECT DATEADD(WEEK,-1, DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0))) [Start of Prior Week]
-- End of Prior Week
SELECT DATEADD(d,-1, DATEADD(d,-1,DATEADD(WEEK, DATEDIFF(WEEK, 0, @Date), 0))) [End of Prior Week]

SQL Agent Jobs without notifications

All of our SQL Agent Jobs should notify someone if a failure occurs.  Below is a query to find all SQL Agent Jobs that are enabled, and do NOT have an email notification.  Quickly dissecting the script below, we are looking for [notify_level_email] NOT IN (1, 2, 3).

1 = When the job succeeds
2 = When the job fails
3 = When the job completes