Search code examples
sqlsql-serverdatediffdateadd

How to write a variable within SQL using Dateadd and DateDiff for finding the last TWO days of the previous month

I am trying to write a variable using the dateadd and datediff that shows the last two days of previous month. One variable will be the second to last day of the previous month, the one I am having trouble with. The other will be the last day of the previous month, the one I was able to get. I am using SQL Server.

I've tried looking for it on Stack and I have only seen the last day of the previous month given and NOT the second to last day. I tried learning the dateadd and datediff, (which I still want to do).

This is what I tried so far: 

Declare @CurrentMonth as date = '3/1/2019'
Declare @SecLastDayPrevMonth as date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @currentmonth), -2) 
Declare @LastDayPrevMonth as date = DATEADD(MONTH, DATEDIFF(MONTH, 0, @currentmonth), -1)

The results I am getting for the seclastdayPrevMonth is 2/28/2019. Instead I would want 2/27/2019

I am also getting 2/28/2019 for lastdayprevmonth which is what I want.

I am writing variables because the current month will change every month, and instead of having to update the other days I need within my query, I want to use variables so I am only updating the current month and everything else is flowing through.

And explanation as to why my dateadd/datediff is wrong and an explanation for why the correct dateadd/datediff is the way it is, will be very helpful

Solution

  • Why not refer to the last day when calculating the second last day? Also, your usage of DATEADD is very weird. The syntax is DATEADD(interval, increment, datetime)

    Declare @recmonth as date = '3/1/2019'
Declare @LastDayPrevMonth as date = EOMONTH(DATEADD(MONTH, -1, @RecMonth))
Declare @SecLastDayPrevMonth as date = DATEADD(DAY, -1, @LastDayPrevMonth)


SELECT @SecLastDayPrevMonth, @LastDayPrevMonth

    So we can calculate the last day of the previous month by subtracting one month from a date and then calling EOMONTH, which returns the last day of a given month. Then the second last day is just subtracting one day from that.

    Yields:

    SecLastDayPrevMonth LastDayPrevMonth
------------------- ----------------
2019-02-27          2019-02-28