CODEWITHSUNDEEP
sql-server
sryaoperations
sryaoperations

Reputation: 1

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

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?

Upvotes: 0

Views: 445

Answers (1)

Thom A
Thom A

Reputation: 95571

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))

Upvotes: 0

Related Questions