Get date range of last quarter

Question

Post reply

Get date range of last quarter

SQL_Surfer

SSCrazy Eights

Points: 8320
More actions
January 17, 2012 at 9:53 am

#144874

Need some help. If I give it a today's date, it should return the date range of last quarter.
Any idea?

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Is this what you are searching for?

Select dateadd(qq, datediff(qq, 0, @ThisDate), 0)

Whoops that gives you this quarter NOT last quarter ... sorry about that

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 2

Or something like this if he wants the start and end date of the quarter.

SELECT

convert(date, DATEADD(q, DATEDIFF(q,0,GETDATE()),0)) FirstQDate,

convert(date, DATEADD(s,-1,DATEADD(q, DATEDIFF(q,0,GETDATE())+1,0))) LastQDate

Woops, I made the same mistake as bitbucket, lol, this gets you this quarter's range. A -1 in the right spot will get you last quarter though.

Edit 2:

Like this:

SELECT

convert(date, DATEADD(q, DATEDIFF(q,0,GETDATE()) -1 ,0)) FirstQDate,

convert(date, DATEADD(s,-1,DATEADD(q, DATEDIFF(q,0,GETDATE()),0))) LastQDate

Just replace the GETDATE() with whatever date you are looking at.

sharmainbox SSC Rookie Points: 31 More actions · Answer 3

SELECT Dateadd(d,-1,convert(date, DATEADD(q, DATEDIFF(q,0,GETDATE()),0))) LastDateOfLastQuater

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

The following should help:

d e c la r e -- spaced to post from work

@ThisDate date = getdate();

select

@ThisDate,

dateadd(quarter,datediff(quarter,0,@ThisDate),0) CurrentQuarterStart,

dateadd(quarter,datediff(quarter,0,@ThisDate) - 1,0) PreviousQuarterStart,

dateadd(quarter,datediff(quarter,0,@ThisDate),-1) PreviousQuarterEnd;

Personally, I would use >= PreviousQuarterStart and < CurrentQuarterStart for a filter in a WHERE clause.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Lynn Pettis (6/2/2016)
The following should help:
d e c la r e -- spaced to post from work
@ThisDate date = getdate();
select
@ThisDate,
dateadd(quarter,datediff(quarter,0,@ThisDate),0) CurrentQuarterStart,
dateadd(quarter,datediff(quarter,0,@ThisDate) - 1,0) PreviousQuarterStart,
dateadd(quarter,datediff(quarter,0,@ThisDate),-1) PreviousQuarterEnd;
Personally, I would use >= PreviousQuarterStart and < CurrentQuarterStart for a filter in a WHERE clause.

+1 for the simplicity and + 1,000,000 for the WHERE clause example to avoid accidental inclusion of whole dates for the next quarter.

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