Start of month last year

Question

Start of month last year

craig.jenkins

SSCommitted

Points: 1643
More actions
September 27, 2017 at 6:41 am

#323756

Hi Guys,
I have a query which works great and bascially brings back data from the start on the current month to the previous day, Is this a way to do the exact same thing but based on last years date?
Thanks you
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
where DBO.LogFile.Created >= DATEADD(month, DATEDIFF(month, 0, @mydate), 0)
AND DBO.LogFile.Created < Convert(Date, GetDate())

Viewing 12 posts - 1 through 12 (of 12 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

craig.jenkins - Wednesday, September 27, 2017 6:41 AM
Hi Guys,
I have a query which works great and bascially brings back data from the start on the current month to the previous day, Is this a way to do the exact same thing but based on last years date?
Thanks you
DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
where DBO.LogFile.Created >= DATEADD(month, DATEDIFF(month, 0, @mydate), 0)
AND DBO.LogFile.Created < Convert(Date, GetDate())

Not sure what you mean by "last year's date". This seems too obvious:

SELECT LastYear = DATEADD(YEAR,-1,GETDATE())

Thom A SSC Guru Points: 99143 More actions · Answer 2

Phil Parkin - Wednesday, September 27, 2017 7:08 AM
Not sure what you mean by "last year's date". This seems too obvious:
SELECT LastYear = DATEADD(YEAR,-1,GETDATE())

I think the OP might want the start of the month for today last year. So, for today, 27 September 2017, to return 01 September 2016. Thus, slightly amending Phil's Code:
SELECT LastYear = DATEADD(MONTH,DATEDIFF(MONTH, 0,DATEADD(YEAR,-1,GETDATE())),0);

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

craig.jenkins SSCommitted Points: 1643 More actions · Answer 3

sorry for the confusion, so what i was looking for would be the 01/09/2016 to 26/09/2016. Hope that makes sense.

how would i amend the below? sorry new to SQL

thank you

where DBO.LogFile.Created >= DATEADD(month, DATEDIFF(month, 0, @mydate), 0)
AND DBO.LogFile.Created < Convert(Date, GetDate())

Phil Parkin SSC Guru Points: 247180 More actions · Answer 4

craig.jenkins - Wednesday, September 27, 2017 7:38 AM
sorry for the confusion, so what i was looking for would be the 01/09/2016 to 26/09/2016. Hope that makes sense.
how would i amend the below? sorry new to SQL
thank you
where DBO.LogFile.Created >= DATEADD(month, DATEDIFF(month, 0, @mydate), 0)
AND DBO.LogFile.Created < Convert(Date, GetDate())

DATEADD(month, DATEDIFF(month, 0, @mydate), 0) gives you the start of the month for @mydate.

To subtract one year from this, use DATEADD again, as follows:

DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @mydate), 0))

Use the same technique in the second part of your WHERE clause too.

craig.jenkins SSCommitted Points: 1643 More actions · Answer 5

Thanks for that, i have amended to the below buts its not bring back the correct data. Any ideas?

where DBO.LogFile.Created >= DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @mydate), 0))
AND DBO.LogFile.Created < Convert(date, (DATEADD(year, -1, getdate())))

Thom A SSC Guru Points: 99143 More actions · Answer 6

craig.jenkins - Wednesday, September 27, 2017 8:03 AM
Thanks for that, i have amended to the below buts its not bring back the correct data. Any ideas?
where DBO.LogFile.Created >= DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @mydate), 0))
AND DBO.LogFile.Created < Convert(date, (DATEADD(year, -1, getdate())))

Convert(date, (DATEADD(year, -1, getdate()))) will return '2016-09-27'. Have a look at what I posted above.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ScottPletcher SSC Guru Points: 101200 More actions · Answer 7

WHERE dbo.LogFile.Created >= DATEADD(month, DATEDIFF(month, 0, @mydate) - 12, 0) AND
dbo.LogFile.Created < DATEADD(day, DATEDIFF(day, 0, DATEADD(month, -12, cast(@mydate AS date))), 0)

Edit: Corrected calc of ending date.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 247180 More actions · Answer 8

craig.jenkins - Wednesday, September 27, 2017 8:03 AM
Thanks for that, i have amended to the below buts its not bring back the correct data. Any ideas?
where DBO.LogFile.Created >= DATEADD(YEAR, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @mydate), 0))
AND DBO.LogFile.Created < Convert(date, (DATEADD(year, -1, getdate())))

This date logic is fine

So what do you mean by 'correct'. Is this query selecting rows outside of this date range?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 9

Just my 2cents:

SELECT DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()) - 12, 0), DATEADD(YEAR,-1,CAST(GETDATE() AS DATE))

craig.jenkins SSCommitted Points: 1643 More actions · Answer 10

Thank you all for the support all sorted. Appreciate it

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

craig.jenkins - Thursday, September 28, 2017 12:57 AM
Thank you all for the support all sorted. Appreciate it

Two way street here... please post the code you finally ended up with.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)