Post reply

Finding previous even numbered month and appropriate year from given date

  • March 25, 2014 at 6:04 pm

    #289735

    Hi,

    I'm trying to write some T-SQL to return the previous even numbered month and appropriate year from given date.

    Examples given:

    03-25-2014 should return 02-xx-2014

    01-01-2014 should return 12-xx-2013

    Thanks in advance,

    Tony

  • March 25, 2014 at 6:33 pm

    #1700104

    I'm not sure what would happen with an even month, so I'm giving you 2 options.

    WITH Dates AS(

    SELECT CAST( '20140425' AS date) somedate UNION ALL

    SELECT CAST( '20140325' AS date) somedate UNION ALL

    SELECT CAST( '20140225' AS date) somedate UNION ALL

    SELECT CAST( '20140125' AS date) somedate

    )

    SELECT somedate,

    DATEADD( MM, (MONTH( somedate) % 2) - 2, somedate) Option1,

    DATEADD( MM, - (MONTH( somedate) % 2) , somedate) Option2

    FROM Dates

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 25, 2014 at 6:39 pm

    #1700106

    Good to know I got a similar solution...

    SELECT SampleDate

    , CASE WHEN MONTH(SampleDate)%2=0 THEN SampleDate

    ELSE DATEADD(m,-1,SampleDate) END

    FROM

    (

    SELECT CAST('03-25-2014' AS DATE) AS SampleDate

    UNION ALL

    SELECT CAST('01-01-2014' AS DATE)

    ) x;

Viewing 3 posts - 1 through 2 (of 2 total)

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