Post reply

Adding two month

  • October 15, 2014 at 3:35 pm

    #311820

    This statement adds two additional months to which is fine :

    DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)

    but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.

  • October 15, 2014 at 4:17 pm

    #1752697

    I'm leaving 2 options in here. The problem is that you're formatting your date in different parts.

    Date formatting should be done in the front end to avoid problems.

    CREATE TABLE DateTest(

    date date

    )

    INSERT INTO DateTest

    VALUES

    ('20141015'),

    ('20141115'),

    ('20141215'),

    ('20150115')

    SELECT DATENAME(MM,DATEADD( mm, 2, dd.date))+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,DATEADD( mm, 2, dd.date))

    FROM DateTest dd

    SELECT DATENAME(MM,nd.NewDate)+ ' ' + DATENAME(D,nd.NewDate) + ', ' + DATENAME(YY,nd.NewDate)

    FROM DateTest dd

    CROSS APPLY (SELECT DATEADD( mm, 2, dd.date) NewDate)nd

    GO

    DROP TABLE DateTest

    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

  • October 15, 2014 at 10:50 pm

    #1752721

    Further on Luis's post, do only one dateadd operation and use convert for the formatting.:cool:

  • October 16, 2014 at 7:44 am

    #1752862

    Previous replies notwithstanding

    STUFF(CONVERT(varchar(12),DATEADD(month,2,dd.date),107),1,3,DATENAME(MM,DATEADD(month,2,dd.date)))

    Far away is close at hand in the images of elsewhere.
    Anon.

  • October 16, 2014 at 12:34 pm

    #1752941

    If you can live with only a three-char month, you can remove the STUFF() function. If you need to have the full month name, you'll need the STUFF() part as well.

    SELECT STUFF(CONVERT(varchar(30), NewDate, 107), 1, 3, DATENAME(MONTH, NewDate))

    FROM your_table dd

    CROSS APPLY (

    SELECT DATEADD(MONTH, 2, dd.date) AS NewDate

    ) AS assign_alias_names

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 16, 2014 at 1:13 pm

    #1752948

    Thanks for your suggestion, it help. Using that statement you provided, if I want to subtract -7 day or 1 week in addition could I just add -7 just before "1,3"?

  • October 16, 2014 at 1:17 pm

    #1752949

    kd11 (10/16/2014)

    Thanks for your suggestion, it help. Using that statement you provided, if I want to subtract -7 day or 1 week in addition could I just add -7 just before "1,3"?

    No, you would do the day/date adjustments in the CROSS APPLY section.

    The "1,3" is removing the first three characters of the 107 format. The 107 format is mmm dd, yyyy, such as Oct 16, 2014. Since you want the full month name, I overlay (STUFF) the first three characters with the entire month name, using "1, 3, DATENAME(MONTH, ...".

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • October 16, 2014 at 2:15 pm

    #1752966

    Looking at books online to get an understanding/some idea with Cross Apply with dates.

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

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