0

In SQL, I am looking to extract data that is only applicable to the previous month. This I have done using the DATEPART function and it works fine where I check the 'M' in the DATEPART function to be -1 from the current Date. But I then have issues when I get to January as there is of course no month that is 1 less than 1.

Is there a way to avoid issues in SQL come January that I am not aware of or cannot think of?

My current syntax is as follows...

(DATEPART(M,p.renewal_date_key) = DATEPART(M,dateadd(M,-1,getdate()))

I then have a further DATEPART clause that does the same for the Year to ensure the Year value is the same as the current year.

Any thoughts on an alternative approach to avoid January issues would be a great help.

This is use in Microsoft as well by the way (so SQL Server and also used in a few historic Excel Power Querys)

raven
  • 2,381
  • 2
  • 20
  • 44
Paul Harper
  • 51
  • 2
  • 7
  • Does this answer your question? [Get the last day of the month in SQL](https://stackoverflow.com/questions/1051488/get-the-last-day-of-the-month-in-sql) – raven Apr 20 '21 at 09:43
  • 1
    How much effort do you save by using "M" in your datepart function rather than "month"? And how much easier is the latter to read and understand? Especially if you add some whitespace to your code rather than cramming it together? – SMor Apr 20 '21 at 11:07

3 Answers3

1

The best thing, from an accuracy and performance perspective, is to calculate a start-date and end-date of the previous month and then use those dates to compare to your dates. You want to avoid using functions on your columns because that rules out the possibility of using indexes (i.e. the query is no longer sargable).

Here is an example which I use as a function to avoid re-coding.

This also handles both date and datetime datatypes due to the way the compare is carried out.

declare @StartDate date = dateadd(day, 1, eomonth(sysdatetime(),-2)), @EndDate date = eomonth(sysdatetime(),-1);

select *
from MyTable
where MyDate >= @StartDate 
and MyDate < dateadd(day, 1, @EndDate);

or you can inline it:

select *
from MyTable
where MyDate >= dateadd(day, 1, eomonth(sysdatetime(),-2)) 
and MyDate < dateadd(day, 1, eomonth(sysdatetime(),-1));
Dale K
  • 25,246
  • 15
  • 42
  • 71
  • That `convert` line looks horrible, why not `DATEFROMPARTS` or `EOMONTH`? – Charlieface Apr 20 '21 at 11:05
  • And why use datetime datatypes when OP is only concerned about dates? And sysdatetime returns a datetime2. – SMor Apr 20 '21 at 11:11
  • @SMor because I prefer generic solutions where possible and this can be used in any date/datetime/datetime2 situation. – Dale K Apr 20 '21 at 19:44
  • @Charlieface old code, if it ain't broke don't fix it :) but I will since you pointed it out – Dale K Apr 20 '21 at 19:44
1

Use the function EOMONTH().

With EOMONTH(renewal_date_key) you get date of the the last day of renewal_date_key's month and with EOMONTH(GETDATE(), -1) the last day of the previous month.
Write the WHERE clause like this:

WHERE EOMONTH(renewal_date_key) = EOMONTH(GETDATE(), -1)

Or if renewal_date_key's data type is DATE:

WHERE renewal_date_key > EOMONTH(GETDATE(), -2)
  AND renewal_date_key <= EOMONTH(GETDATE(), -1)
forpas
  • 160,666
  • 10
  • 38
  • 76
0

The simplest method is:

 where datediff(month, p.renewal_date_key, getdate()) = 1

However, that is not index friendly. For an optimizer friendly version, you can construct the dates using datefromparts():

where p.renewal_date_key < datefromparts(year(getdate()), month(getdate()), 1) and
      p.renewal_date_key >= dateadd(month, -1, datefromparts(year(getdate()), month(getdate()), 1))

Note that these version work even when the "date" column includes a time component.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786