First and last day of this month last year

Question

I'm declaring some months and dates and need help on getting the first and last date of this month last year.

So far I have the following.

DECLARE @today date Set @today = cast (getdate() as date)
DECLARE @firstdaylastmonth date Set @firstdaylastmonth = DATEADD(month, DATEDIFF(month, 0, @today)-1, 0)
DECLARE @lastdaylastmonth date SET @lastdaylastmonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today),0))
DECLARE @lastdaythismonth date SEt @lastdaythismonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today)+1,0)) 

-- DECLARE @firstdaythismonthlastyear SET 
-- DECLARE @lastdaythismonthlastyear SET


Select 
@today,
@firstdaylastmonth,
@lastdaylastmonth,
@lastdaythismonth

-- @firstdaythismonthlastyear
-- @lastdaythismonthlastyear

But am having trouble finding the last 2 declarations. Help appreciated.

I am pretty sure that stack has a lot of answers on this question already. 2 seconds in google and you will get plenty of answers — Giorgi Nakeuri, Apr 29 '15 at 16:30
@GiorgiNakeuri - well, if you can find an exact dupe, vote to close this question as a dupe. — Jamiec, Apr 29 '15 at 16:33
@Jamiec, for example http://stackoverflow.com/questions/7429885/sql-query-to-find-last-day-of-current-month http://stackoverflow.com/questions/13437362/how-to-get-first-and-last-date-of-current-year — Giorgi Nakeuri, Apr 29 '15 at 17:10

Stavros Zotalis · Answer 1 · 2015-04-29T16:32:39.117

1

You can use the EOMONTH ( start_date [, month_to_add ] ) Function For SQL Server 2012 and above.

edited Apr 29 '15 at 16:32

answered Apr 29 '15 at 16:23

Stavros Zotalis

718
4
24

Great! The code using this function will look like: `DECLARE @firstdaythismonthlastyear date SET @firstdaythismonthlastyear = DATEADD(DAY, 1, EOMONTH(DATEADD(YEAR, -1, GETDATE()), -1)) DECLARE @lastdaythismonthlastyear date SET @lastdaythismonthlastyear = DATEADD(YEAR, -1, EOMONTH(GETDATE())) SELECT @firstdaythismonthlastyear, @lastdaythismonthlastyear` – Akshay Rane Apr 30 '15 at 04:47

score 0 · Answer 2 · answered Apr 29 '15 at 16:24

This does what you asked for

DECLARE @firstdaythismonthlastyear date 
SET @firstdaythismonthlastyear = DATEADD(year,-1,DATEADD(month, DATEDIFF(month, 0, @today), 0))
DECLARE @lastdaythismonthlastyear date 
SET @lastdaythismonthlastyear = DATEADD(year,-1,@lastdaythismonth)

The first builds on what you had done for @firstdaylastmonth and the second uses your precalculated @lastdaythismonth.

score 0 · Answer 3 · answered Apr 29 '15 at 16:25

You can use dateadd to offset by 12 months:

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101)) ,
'Last Day of Previous Month, Last Year'
UNION
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)) AS Date_Value,
'First Day of Current Month, Last Year' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)) ,
'Last Day of Current Month, Last Year'
UNION
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101)) ,
'First Day of Next Month, Last Year'

Why are you converting dates back and forth to `VARCHAR`? – Jamiec Apr 29 '15 at 16:28 — Jamiec, Apr 29 '15 at 16:28

Akshay Rane · Answer 4 · 2015-04-29T17:39:59.223

We can CAST the first day of current month into a date and subtract one year for @firstdaythismonthlastyear, and just subtract a year from @lastdaythismonth to get @lastdaythismonthlastyear.

DECLARE @today date Set @today = cast (getdate() as date)
DECLARE @firstdaylastmonth date Set @firstdaylastmonth = DATEADD(month, DATEDIFF(month, 0, @today)-1, 0)
DECLARE @lastdaylastmonth date SET @lastdaylastmonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today),0))
DECLARE @lastdaythismonth date SEt @lastdaythismonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today)+1,0)) 

DECLARE @firstdaythismonthlastyear date SET @firstdaythismonthlastyear = DATEADD(year, -1,CAST(CONCAT(YEAR(@today),'-',MONTH(@today),'-',1) as date))
DECLARE @lastdaythismonthlastyear date SET @lastdaythismonthlastyear = DATEADD(year, -1, @lastdaythismonth)

Select 
@today,
@firstdaylastmonth,
@lastdaylastmonth,
@lastdaythismonth,
@firstdaythismonthlastyear,
@lastdaythismonthlastyear

First and last day of this month last year

4 Answers4