query results of last full month's data

Question

query results of last full month's data

DaveBriCam

SSCarpal Tunnel

Points: 4298
More actions
February 20, 2023 at 1:35 pm

#4151973

Thanks in advance for any help. I've found similar topics but not quite sure how to do this one.
I need at any given time to return the results of the last full month's data regardless of how many days are in that month, e.g. today is February 20, 2023 and I want to return only January 1-31, 2023. Any ideas on the syntax for this select?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 1

I tried this code and it's not perfect but works, but I'm sure there's a better way:

declare @StartDate DATETIME, @EndDate DATETIME
SET @StartDate = dateadd(mm, -1, getdate())
SET @StartDate = dateadd(dd, datepart(dd, getdate())*-1, @StartDate)
SET @EndDate = dateadd(mm, 1, @StartDate)
select @StartDate
select @EndDate

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 2

month

Here is the code produced by ChatGPT

SELECT *
FROM your_table
WHERE date_column >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0)
  AND date_column < DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)
;

DaveBriCam SSCarpal Tunnel Points: 4298 More actions · Answer 3

I found this to work perfectly:

  where ServiceDate between DATEADD (mm, DATEDIFF (mm, 0, GETDATE ()) - 1, 0) and 
  dateadd (dd, -1, DATEADD (mm, DATEDIFF (mm, 0, GETDATE ()), 0));

Jonathan AC Roberts SSCoach Points: 18355 More actions · Answer 4

DaveBriCam wrote:

I found this to work perfectly:

  where ServiceDate between DATEADD (mm, DATEDIFF (mm, 0, GETDATE ()) - 1, 0) and 
  dateadd (dd, -1, DATEADD (mm, DATEDIFF (mm, 0, GETDATE ()), 0));

Looks like ChatGPT missed the last day of the month.