Post reply

datetime to month - year

  • March 19, 2009 at 10:14 am

    #129259

    How convert to do Datetime to month-year

    input -

    2009-01-23 1:12:52.253

    output needed-

    Jan-2009?

    I tried by datepart but not got requried results.

  • March 19, 2009 at 10:20 am

    #962875

    try..

    SELECT DATENAME(mm,'2009-01-23 1:12:52.253') + '-' + datename(yy,'2009-01-23 1:12:52.253')

  • March 19, 2009 at 10:22 am

    #962879

    Will this work for you?

    select left(datename(mm, getdate()), 3) + '-' + cast(datename(yyyy, getdate()) as char(4))

  • March 19, 2009 at 10:55 pm

    #963311

    The question I would ask is where are you displaying the output. Is it in Excel, reporting services? I gather you want to get sales per month or something similar for this. My query would look something like this.

    SELECT DATEADD( MONTH, DATEDIFF( MONTH, 0, '11-Jan-2009'), 0)

    Then the aggregation on that, and in the report I would format it to be MMM-yyyy.

    My opinion

  • March 19, 2009 at 11:30 pm

    #963317

    Lynn Pettis (3/19/2009)

    Will this work for you?

    select left(datename(mm, getdate()), 3) + '-' + cast(datename(yyyy, getdate()) as char(4))

    No casting needed when you used DATENAME, Lynn. 🙂

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

  • December 15, 2010 at 8:59 am

    #1262941

    Jeff -

    How then would one be able to sort chronologically on this field? Is there a way to keep the format while changing the data type to a numeric format?

  • December 16, 2010 at 7:10 am

    #1263438

    andell_ramsay (12/15/2010)

    Jeff -

    How then would one be able to sort chronologically on this field? Is there a way to keep the format while changing the data type to a numeric format?

    To answer the questions above in order... You can't and yes. 😛

    See the following SQL Spackle article for one way to do this...

    http://www.sqlservercentral.com/articles/T-SQL/71511/

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

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

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