Get latest occurrence of X month.

Question

Post reply

Get latest occurrence of X month.

Jim Mackenzie

Hall of Fame

Points: 3728
More actions
January 17, 2014 at 3:40 am

#281259

Hi All,
I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')
I know for a fact there's a better way to do it, but need some help. Any suggestions?
Cheers,
Jim.
SQL SERVER Central Forum Etiquette[/url]

Viewing 9 posts - 1 through 8 (of 8 total)

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Since you are using IIF I can safely assume the code is for SQL 2012 or later 🙂

DECLARE @Date DATE = '20130217'

SELECT IIF(MONTH(@Date) <= 3, DATEFROMPARTS(YEAR(@Date)-1,4,1),DATEFROMPARTS(YEAR(@Date),4,1));

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 2

Koen Verbeeck (1/17/2014)
Since you are using IIF I can safely assume the code is for SQL 2012 or later 🙂
DECLARE @Date DATE = '20130217'
SELECT IIF(MONTH(@Date) <= 3, DATEFROMPARTS(YEAR(@Date)-1,4,1),DATEFROMPARTS(YEAR(@Date),4,1));

Yep, it's 2012, probably should have added that in. And I noticed you're already using your semi-colons, very good Koen!

This version is obviously much more succinct and easy to understand than my monstrosity. haha. Thanks very much 🙂

I wonder if there are any other ways?

SQL SERVER Central Forum Etiquette[/url]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

There are probably other methods, as usual.

This one was the first I could come up with.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

JimbobsQL (1/17/2014)
Hi All,
I'm trying to find the most succinct way to get the last occurrence of April 1st given a date.
At the moment I'm using this:
DECLARE @Date DATE = '20131217'
SELECT CONVERT(DATE, CAST(DATEPART(YEAR,
IIF(
--If we're at the start of a year
--we'll need to go back a year
DATEPART(MONTH, @Date) IN (1,2,3),
DATEADD(YEAR, - 1, @Date),
@Date
)) AS VARCHAR(4)) + '0401')
I know for a fact there's a better way to do it, but need some help. Any suggestions?
Cheers,
Jim.

Could you give a better description of what you are trying to accomplish? From the description given here I am not quite sure what you are trying to accomplish.

I'm asking as there may be a better way to achieve what you are attempting to accomplish.

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 5

Hi Lynn,

Each day we're going to run an SSRS report to pull out all sales after the 1st of April. So no matter what GETDATE() returns we need to be showing the last occurrence of 01/Apr (The start of the current UK financial year).

E.g.

10/Apr/2013 would return a value of 1/Apr/2013 (Financial year 2013)

1/Jan/2014 would return a value of 1/Apr/2013 (Financial year 2013)

3/Apr/2014 would return a value of 1/Apr/2014 (Financial year 2014)

If you pass in a date to the query in my original post (or Koen's) you'll see that the query works, it's just that mine is a bit bulky.

Does that make a bit more sense, or have I made things worse? haha

SQL SERVER Central Forum Etiquette[/url]

Jim Mackenzie Hall of Fame Points: 3728 More actions · Answer 6

Koen Verbeeck (1/17/2014)
There are probably other methods, as usual.
This one was the first I could come up with.

Cracking effort, much better than my first attempt!

SQL SERVER Central Forum Etiquette[/url]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 7

JimbobsQL (1/17/2014)
Koen Verbeeck (1/17/2014)
There are probably other methods, as usual.
This one was the first I could come up with.
Cracking effort, much better than my first attempt!

I already had your query as inspiration 😉

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

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

How is this?

declare @TestDate date = getdate();

select dateadd(month,3,dateadd(year,datediff(year,0,dateadd(month,-3,@TestDate)),0))

set @TestDate = '20140413';

select dateadd(month,3,dateadd(year,datediff(year,0,dateadd(month,-3,@TestDate)),0))

set @TestDate = '20140331';

select dateadd(month,3,dateadd(year,datediff(year,0,dateadd(month,-3,@TestDate)),0))

set @TestDate = '20140401';

select dateadd(month,3,dateadd(year,datediff(year,0,dateadd(month,-3,@TestDate)),0))