eed to get previous quarter date from current date

  • BKW

    Ten Centuries

    Points: 1350

    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

  • Jack Corbett

    SSC Guru

    Points: 184373

    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

    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

    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 3188

    SSC Guru

    Points: 88122

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

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • BKW

    Ten Centuries

    Points: 1350

    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

    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: 26

    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: 442144

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

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