Post reply

The best way to add next month

  • April 15, 2010 at 8:03 pm

    #223321

    Please look at the follow code:

    - The variable @Date has the value is 201012 (yyyymm)

    - Now, I want to increse the month(mm) in the @Date variable to 1

    I had done this thing by 2 ways as the following code:

    DECLARE @Date int = 201012

    -- way 1

    SELECT CONVERT(INT,SUBSTRING(CONVERT(CHAR(8),DATEADD(MONTH,1,Convert(Date,CONVERT(char(8),CONVERT(CHAR(6),@Date)+'01'),8)),112),1,6))

    -- way 2

    SELECT CASE WHEN CONVERT(INT,SUBSTRING(CONVERT(CHAR(6),@Date),5,2))>11 THEN @Date+89 ELSE @Date + 1 End

    Do you have any good way which is better than 2 ways above ?:-D

    http://unitech.vn

  • April 16, 2010 at 12:30 am

    #1152599

    U can try this

    SELECT

    CASE WHEN (@Date % 100) = 12 THEN

    ((@Date / 100) + 1) * 100 + 1

    ELSE

    (@Date / 100) * 100 + (@Date % 100) + 1

    END

    Eralper

    SQL Server T-SQL

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • April 16, 2010 at 12:39 am

    #1152601

    :w00t: oh, good SQL. I'm rewrite your SQL and...

    SELECT

    CASE WHEN (@Date % 100) = 12 THEN

    @Date + 89

    ELSE

    @Date + 1

    END

    Thanks for your support, 😀

    Any one else, huhu.

    http://unitech.vn

  • April 16, 2010 at 12:44 am

    #1152602

    Hi nguyennd,

    You are genius 🙂

    I was so detailed with mod, division, etc.

    You made the query more simple.

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • April 16, 2010 at 10:38 am

    #1153011

    The best way to deal with date values is to store it as date values.

    So, instead of having an integer value it woul be a lot better and easier to store it as datetime or date (if you're on SS2K8).

    Just my 2 cents...


    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]

  • April 16, 2010 at 10:47 am

    #1153015

    Just another option:

    select convert(char(6), dateadd(mm, 1, dateadd(mm, right(@Date, 2) - 1, dateadd(yy, cast(left(@Date,4) as int) - 1900, 0))), 112)

  • April 16, 2010 at 11:04 am

    #1153030

    select

    *,

    NextMonth =

    convert(char(6),dateadd(mm,(((DT/100)-1900)*12)+(Dt%100),0),112)

    from

    ( -- Test Data

    select DT = 201011 union all

    select DT = 201012 union all

    select DT = 201101

    ) a

    Results:

    DT NextMonth

    ----------- ---------

    201011 201012

    201012 201101

    201101 201102

    (3 row(s) affected)

  • April 16, 2010 at 11:12 am

    #1153033

    nguyennd (4/16/2010)

    :w00t: oh, good SQL. I'm rewrite your SQL and...

    SELECT

    CASE WHEN (@Date % 100) = 12 THEN

    @Date + 89

    ELSE

    @Date + 1

    END

    Thanks for your support, 😀

    Any one else, huhu.

    Shorter?

    select @Date+case when @date%100=12 then 89 else 1 end

  • April 18, 2010 at 2:47 am

    #1153448

    Michael Valentine Jones (4/16/2010)

    Shorter?

    select @Date+case when @date%100=12 then 89 else 1 end

    Even shorter?

    SELECT @Date+1+88*((@date%100)/12)

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • April 18, 2010 at 3:18 pm

    #1153511

    hi

    This is my way..

    declare @date varchar(100)='201012'

    select convert(varchar(6),dateadd(M,1,convert(date,@date+'01')),112)

  • April 18, 2010 at 6:17 pm

    #1153530

    vmssanthosh (4/18/2010)

    hi

    This is my way..

    declare @date varchar(100)='201012'

    select convert(varchar(6),dateadd(M,1,convert(date,@date+'01')),112)

    That's fine - but why use VARCHAR for fixed-length data? 😉

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

  • April 18, 2010 at 10:12 pm

    #1153573

    lmu92 (4/16/2010)

    The best way to deal with date values is to store it as date values.

    So, instead of having an integer value it woul be a lot better and easier to store it as datetime or date (if you're on SS2K8).

    Just my 2 cents...

    Ditto. I'll also add that since we're adding months one at a time, there's probably a cursor or While Loop involved somewhere here. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • April 19, 2010 at 12:16 am

    #1153602

    🙂

    After Michael, Paul has the shortest statement ever ... Great!

    Eralper
    SQL Server and T-SQL Tutorials and Articles
    Microsoft Certification and Certification Exams

  • April 19, 2010 at 8:00 am

    #1153846

    Paul White NZ (4/18/2010)

    Michael Valentine Jones (4/16/2010)

    Shorter?

    select @Date+case when @date%100=12 then 89 else 1 end

    Even shorter?

    SELECT @Date+1+88*((@date%100)/12)

    :Whistling:

    SELECT @Date+1+88*(@date%100/12)

  • April 19, 2010 at 4:55 pm

    #1154202

    Michael Valentine Jones (4/19/2010)

    :Whistling:

    SELECT @Date+1+88*(@date%100/12)

    :laugh: :laugh: :laugh: :laugh: :laugh:

    Nice one.

    Paul White
    SQLPerformance.com
    SQLkiwi blog
    @SQL_Kiwi

Viewing 15 posts - 1 through 14 (of 14 total)

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