sqlsql-servert-sqldatetimedayofmonth

SQL date comparison using case; for month to date reporting


The goal is to provide month to date (MTD) reporting, based on a list MTD dates created by an SQL query. however when we're on the 1st day of the month my queries don't work (the list of dates aren't relevant)

the query I use to create a month to date list of dates which I later in an inner join query to extract month to date transactions.

DECLARE @Date1 DATE, @Date2 DATE
SET @Date1 = (SELECT dateadd(mm,-1, eomonth(getdate())))
SET @Date2 = GETDATE()

SELECT DATEADD(DAY,number+1,@Date1) [Date] 
FROM master..spt_values
WHERE type = 'P'AND DATEADD(DAY,number+1,@Date1) < @Date2
Order By [Date] ASC

Now that we find ourselves in the beginning of the new month, I simply want a list of all dates from the previous month, i.e from the 1st up until the 30 of April.

So the I'm hoping that an IF/CASE clause will do the trick BUT its not working. Somehow the date comparison is not working. Here's the query below (note that I plan to add this CASE clause to the query above):

SELECT(CASE WHEN (SELECT GETDATE()) = (SELECT dateadd(mm, -1,dateadd(dd, +1, eomonth(getdate())))) THEN (SELECT dateadd(mm,-2,dateadd(dd, +1,eomonth(getdate()))))  ELSE(SELECT dateadd(mm, -1,dateadd(dd, +1, eomonth(getdate())))) END)

So, in summary, I'd like to add a CASE clause to query 1, this CASE/IIF would compare today with the first day of any given month and if today just so happens to the 1st day of the current month, then simply give me the first day of the previous month. But the comparison just doesn't work.


Solution

  • Query:

     DECLARE @Date1 DATE, @Date2 DATE 
        SET @Date1 = (SELECT CASE 
             WHEN DAY(GETDATE()) = 1 
             THEN DATEADD(month, -1, DATEADD(day, 1 - DAY(GETDATE()), CAST(GETDATE() AS date)))
             ELSE DATEADD(day, 1 - DAY(GETDATE()), CAST(GETDATE() AS date))
           END
        ) 
    SET @Date2 = GETDATE() 
    
    SELECT DATEADD(DAY,number,@Date1) [Date] 
    FROM master..spt_values 
    WHERE type = 'P'
        AND DATEADD(DAY,number,@Date1) < @Date2 
    ORDER BY [Date] ASC
    

    Output:

    Date
    2023-04-01
    2023-04-02
    2023-04-03
    2023-04-04
    2023-04-05
    2023-04-06
    2023-04-07
    2023-04-08
    2023-04-09
    2023-04-10
    2023-04-11
    2023-04-12
    2023-04-13
    2023-04-14
    2023-04-15
    2023-04-16
    2023-04-17
    2023-04-18
    2023-04-19
    2023-04-20
    2023-04-21
    2023-04-22
    2023-04-23
    2023-04-24
    2023-04-25
    2023-04-26
    2023-04-27
    2023-04-28
    2023-04-29
    2023-04-30

    fiddle