eed to get previous quarter date from current date

Question

eed to get previous quarter date from current date

BKW

Ten Centuries

Points: 1350
More actions
July 31, 2008 at 9:36 am

#196381

Can anyone help me with this one.
I need to get the previous quarter from the current date.
example: If today is 07/31/2008 I need to know the previous quarter which would be 06/30/2008.
I can get current quarter but cannot figure out how to get last quarter.
SELECT DATEADD(quarter, DATEDIFF(quarter, -1, GETDATE()), -1) --get last day of current quarter
Changinagain

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

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

I think this will work regardless of the date you pass in.

SELECT DateAdd(Day, -1, DateAdd(quarter, DatePart(Quarter, getdate())-1, '1/1/' + Convert(char(4), DatePart(Year, getdate()))))

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Simon E Doubt SSCarpal Tunnel Points: 4191 More actions · Answer 2

Have you got a table which stores the date ranges for each of your quarters?

Such a table might look like this:

QID QuarterStartDate QEndDate

=== ============= =======

1 Jan 1 2008 Mar 31 2008

2 Apr 1 2008 Jun 30 2008

You could use that table to determine which quarter you're in from the present date, and then subtract 1 from the value of the QID field to get the previous quarter.

Hope that helps a bit.

- Simon

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 3

Nice solution Jack, too me a bit to work it out but I ended up with the same solution 😉

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 4

You were really close, review the following:

select dateadd(quarter, datediff(quarter, 0, getdate()) - 0, 0)

,dateadd(quarter, datediff(quarter, 0, getdate()) - 1, 0)

,dateadd(quarter, datediff(quarter, 0, getdate()) - 2, 0)

,dateadd(quarter, datediff(quarter, 0, getdate()) - 3, 0);

select dateadd(quarter, datediff(quarter, -1, getdate()) - 0, -1)

,dateadd(quarter, datediff(quarter, -1, getdate()) - 1, -1)

,dateadd(quarter, datediff(quarter, -1, getdate()) - 2, -1)

,dateadd(quarter, datediff(quarter, -1, getdate()) - 3, -1);

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

BKW Ten Centuries Points: 1350 More actions · Answer 5

Thanks Jack, that works great. I would have been here all week working that one out (if at all).

Simon, had not thought about storing in a table. Good idea too.

Thanks all;)

Changinagain

Christopher Stobbs SSC-Insane Points: 21098 More actions · Answer 6

Thats the one,

since my last post I been trying to find a way to do it without doing a CHAR cast.

Well done Jeff thats awesome 🙂

----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley

:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]

SQL-4-Life

tomasz.wieczorkowski SSC Rookie Points: 36 More actions · Answer 7

Last year:

(DT_WSTR,4)YEAR(DATEADD("yy",-1,GETDATE()))

Last Quarter:

(DT_WSTR,1)DATEPART("qq",DATEADD("qq",-1,GETDATE()))

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

Here are some routines: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/