I need to pull previous Month's records on the first day of the month without hardcoding in SQL

Question

I have a job that sends out a spreadsheet each day. In my where clause I have the below date range so it will pull month to date:

(CAST(os.[Order Date] as date) >= DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) AND CAST(os.[Order Date] as date) <= CAST(GETDATE()-1 AS DATE))

The above works for every day except the first day of each month because I am pulling information for the previous day's records so I would need the previous month's records and this is trying to capture current month. I'm not sure if this makes sense. Is there anyway I can fix the issue to pull the previous month if it is the first day of a new month?

You can use an IF Statement and check if it's the first day of the month using the DAY function. Then do a different query with a different WHERE clause to reflect you grabbing the previous month. — Ryan Wilson

Larnu Larnu · Accepted Answer · 2020-07-02T15:15:46

If I am reading between the lines correctly, and you want the entirety of yesterday's month then this would work:

WHERE DateColumn >= DATEADD(MONTH,DATEDIFF(MONTH,0,DATEADD(DAY, -1, GETDATE())),0)
  AND DateColumn < CONVERT(date,GETDATE())

Alternatively, for the first clause you could do:

DATEADD(DAY, 1, EOMONTH(DATEADD(DAY, -1, GETDATE()),-1))

I need to pull previous Month's records on the first day of the month without hardcoding in SQL

1 Answers