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 11 (of 11 total)

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

Phil Parkin SSC Guru Points: 246991 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())

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Thom A SSC Guru Points: 99133 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: 246991 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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 99133 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: 101130 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: 246991 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?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

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: 1004432 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)