Post reply

Rolling date extract question

  • May 22, 2011 at 5:12 pm

    #251338

    I am after these five dates, 21 months ago

    Format MM/dd/yyyy

    04/01/XXXX

    07/01/XXXX

    10/01/XXXX

    01/01/XXXX (of the next year)

    Only the above dates can be returned.

    When date is 02/20/2011, then I would want 04/01/2009

    When date is (today) 05/23/2011, then I would want 07/01/2009

    When date is 07/10/2011, then I would want 10/01/2009

    When date is 11/07/2011, then I would want 01/01/2010

    The formula is take the current date, deduct 24 month, but then get the 1st date of the next quarter.

    How do I do this with TSQL ???

    Is it possible to get the date return in the format of MM/dd/yyyy as a varchar(12), Thanks:-)

  • May 22, 2011 at 5:33 pm

    #1328260

    Something like this?

    SELECT DATEADD(yy,-2,DATEADD(qq,DATEDIFF(qq,0,GETDATE())+1,0))


    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • May 22, 2011 at 5:56 pm

    #1328264

    Thanks Lutz :-):-D:-)

    Awesome

  • May 22, 2011 at 6:03 pm

    #1328265

    BTW that tricks works also for years, months and days (to cut off the time out of the date).

  • May 22, 2011 at 10:30 pm

    #1328281

    Ooops I stuffed up the date selection, went forwardm should have gone backwards

    Should be....

    Format MM/dd/yyyy

    01/01/XXXX

    04/01/XXXX

    07/01/XXXX

    10/01/XXXX

    Only the above dates can be returned.

    When date is 02/20/2011, then I would want 01/01/2009

    When date is (today) 05/23/2011, then I would want 04/01/2009

    When date is 07/10/2011, then I would want 07/01/2009

    When date is 11/07/2011, then I would want 10/01/2009

    The formula is take the current date, deduct 24 month, but then get the 1st date of the PREVIOUS quarter.

    Sorry about that, had a fiddle with the code posted, not getting the result I need. any ideas, thanks:-)

  • May 23, 2011 at 1:16 am

    #1328310

    Just a little modification in above query and get the result :

    SELECT CONVERT(VARCHAR(12),DATEADD(yy,-2,DATEADD(qq,DATEDIFF(qq,0,GETDATE()),0)),101)

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

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