CODEWITHSUNDEEP
sqlsql-server
DataCrypt
DataCrypt

Reputation: 307

Obtain date for prior month and prior year issue?

I have the following SQL statement that, for the most part was working for me, or so I thought. I need to return the First Day of the Previous Month in a MM/DD/YYYY format. Now that we're into a new year (January specifically) this isn't correct.

SELECT convert(char(10),DATEADD(year,-1,DATEADD(month, DATEDIFF(MONTH, 0, getdate())-1, 0)),120) AS LastYearFirstOfPreviousMonth

If I use this with the 1st of Feb, on-wards it works great!

SELECT convert(char(10),DATEADD(year,-1,DATEADD(month, DATEDIFF(month, 0, '2018-02-01')-1, 0)),120) AS LastYearFirstOfPreviousMonth

same if it was '2018-03-01', etc. But when I use '2018-01-01', instead of getting '2017-12-01', I get '2016-12-01'.

Ex. Let's say getdate() brings back "07/12/2018", the date I need would be for the 1st of the previous month for the previous year. So it would be "06/01/2017". For "01/03/2018", it would be "12/01/2017". But my statement brings back "12/01/2016".

What's wrong with my logic?

SP excerpt below:

SELECT c.ClientID ... 
INTO PC_Benefits_Expired 
from Client_Program cp 
INNER JOIN .... 
WHERE (CAST(cp.startdate AS DATE) >= CAST(DATEADD(year,-1,DATEADD(month, DATEDIFF(month, 0, getdate()), 0)) AS DATE) 
--OR 
--FirstDayOfCurrentMonthPriorYear CAST(cp.startdate AS DATE) >= CAST(DATEADD(year,-1,DATEADD(month, DATEDIFF(month, 0, getdate())-1, 0)) AS DATE)) 
--FirstDayOfLastMonthPriorYear and CAST(cp.EndDate AS DATE) <= CAST(DATEADD(day,-1,DATEADD(month, DATEDIFF(month, 0, GETDATE())+1, 0)) AS DATE) 
--LastDayOfCurrentMonthCurrentYear and CAST(cp.EndDate AS DATE)='2017-02-28'

Upvotes: 0

Views: 95

Answers (2)

Aaron Dietz
Aaron Dietz

Reputation: 10277

Here is one way:

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

This gets the last day from 2 months ago, then adds one day to it to give you the first day of one month ago.

You can format however you like:

SELECT FORMAT(DATEADD(DAY,1,EOMONTH(GETDATE(),-2)),'MM/dd/yyyy')

Edit:

For the clarified issue, you have two separate conditions. I would just use a CASE statement to handle the January condition differently:

SELECT CASE WHEN DATEPART(MONTH,GETDATE()) = 1
            THEN DATEADD(DAY,1,EOMONTH(GETDATE(),-2))
            ELSE DATEADD(YEAR,-1,DATEADD(DAY,1,EOMONTH(GETDATE(),-2)))
       END AS LastYearFirstOfPreviousMonthDATEADD

Upvotes: 2

Lamak
Lamak

Reputation: 70668

This should work:

SELECT CONVERT(CHAR(10),DATEADD(MONTH,DATEDIFF(MONTH, 0, getdate())-1,0),101)

Upvotes: 0

Related Questions