0

I'm using this code in an SQL query

WHERE [Date] >= DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE())-1,DATEPART(day,GETDATE()))
  AND [Date] <= EOMONTH(DATEFROMPARTS(DATEPART(year,GETDATE()),DATEPART(month,GETDATE())-1,DATEPART(day,GETDATE())));

The problem is come 2020 the December query will through up an error

The code I posted manages the dates between which data will be returned. It looks at the date the code is run and choose that day from last month till the end of last month. What I need is dates from the 1st till the last day of the month prior to the one this code is called in.

I will be working on this issue tomorrow, it will be interesting to see what solutions other people can come up with.

Thom A
  • 88,727
  • 11
  • 45
  • 75
Ronan
  • 23
  • 3
  • 1
    *"The problem is come 2020 the December query will through up an error"*. I don't see any errors. [db<>fiddle](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1fdf88b51f0573f5ca1fcd581c1b5f02) – Thom A Oct 29 '19 at 18:41
  • are you trying to use '2020-12-31'? – LONG Oct 29 '19 at 18:54
  • If you cannot define in text what you are trying to accomplish, you will struggle to write the appropriate code. And you likely don't really understand your goal. If today is Mar 30 2019, what dates do you intend to use as boundaries in your queries? If your goal is to use the same day of the previous month, you will need to explicitly define what to do with these end-of-month issues. – SMor Oct 29 '19 at 22:09
  • Not necessarily correct SMor, being dyslexic I find it considerably more difficult to define my coding problems in English rather than code. The guys below have pointed out to me that DATEADD is what I need for this action. It will allow me to subtract a month from January and get December hopefully. – Ronan Oct 30 '19 at 09:58

2 Answers2

2

try this 

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0),
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) - 1

it will get you the previous month start and end date

JamieD77
  • 13,796
  • 1
  • 17
  • 27
  • Shorter than my version... interesting technique of using 0 as a starting date basis vs an actual date. Otherwise not obvious based on typed parameters expected... Like it... – DRapp Oct 29 '19 at 19:13
0

If you want the previous month:

where date >= dateadd(month, -1, datefromparts(year(getdate(), month(getdate(), 1))) and
      date < datefromparts(year(getdate(), month(getdate(), 1))

This simply checks that it is before the first of the this month and then subtracts a month from that.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Gordon that is a nice solution, I would have defined the end of the month the same way as I defined the start of the month however you have used the comparison to accomplish the same thing. – Ronan Oct 30 '19 at 10:21