T-SQL to get dates

Question

I've SSRS sales report to which I need to pass the dates for previous month's start date and end date which I am able to pass using below code. However, since the sales report has data from the past year(2014) I need to pass the dates for last year as well. The below code gives StartDate1 as 2015-02-01 and EndDate1 as 2015-02-28. I need to get the dates for past year like 2014-02-01 as StartDate2 and 2014-02-28 as EndDate2

SELECT  DATEADD(MONTH, DATEDIFF(MONTH, '19000201', GETDATE()), '19000101') AS StartDate1,
    DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '18991231') AS EndDate1

What version of SQL Server are you on? IF you have 2012+ you can use EOMonth to get the last date of a selected month. https://msdn.microsoft.com/en-us/library/hh213020.aspx — mmarie, Mar 16 '15 at 20:25
There may be an easier way, but you could take the first date of the next month and subtract a day to get the last day of the month you want — Jen R, Mar 16 '15 at 20:43
Actually, here: http://stackoverflow.com/questions/16646585/sql-query-to-find-the-last-day-of-the-month — Jen R, Mar 16 '15 at 20:45

JC Ford · Accepted Answer · 2015-03-17T02:02:23.867

Since last day of month can vary, the important thing is to get the first day of the current month this year. From that you can calculate the other three values.

You could do this easily with expressions in the parameters' default values instead.

Start of Month
=Today.AddDays(1-Today.Day)

End of Month
=Today.AddDays(1-Today.Day).AddMonths(1).AddDays(-1)

Start of Month Last Year
=Today.AddDays(1-Today.Day).AddYears(-1)

End of Month Last Year:
=Today.AddDays(1-Today.Day).AddYears(-1).AddMonths(1).AddDays(-1)

But if you really want to do it in SQL, you can. Note below I'm describing how to get the start of month and then just using placeholder variables for it in the other expressions for clarity.

--Start of Month
dateadd(month, datediff(month, 0, getdate()), 0)

--End of Month
dateadd(day, -1, dateadd(month, 1, @StartOfMonth))

--Start of Month Last Year
dateadd(year, -1, @StartOfMonth)

--End of Month Last Year
dateadd(day, -1, dateadd(month, 1, @StartOfMonthLastYear))

Those are the pieces. Put them together into one giant, very hard to read select statement like so:

select
    dateadd(month, datediff(month, 0, getdate()), 0) StartDate1,
    dateadd(day, -1, dateadd(month, 1, dateadd(month, datediff(month, 0, getdate()), 0))) EndDate1,
    dateadd(year, -1, dateadd(month, datediff(month, 0, getdate()), 0)) StartDate2,
    dateadd(day, -1, dateadd(month, 1, dateadd(year, -1, dateadd(month, datediff(month, 0, getdate()), 0)))) EndDate2

Now you see why I recommend just using internal parameters that can leverage the .NET datetime class methods.

T-SQL to get dates

1 Answers1