Get Month Name

  • Hi all

    Is there any best way to get month name in SQL SERVER 2005.

    I know that we can do this using our own function, but just i want to conform that if SQL SERVER provide any function for month name if we have month number i.e. 1,2..12

    Thanks

    Warm Regards,
    Shakti Singh Dulawat
    Before printing, think about the environment
    Do the impossible, and go home early.

  • SQL has a DateName function, however it takes a datetime as a parameter, not a number

    SELECT DATENAME(month, GETDATE()) AS 'Month Name'

    returns

    Month Name

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

    February

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks

    Thats good

    But is there is way so we can get month name using month number.

    Warm Regards,
    Shakti Singh Dulawat
    Before printing, think about the environment
    Do the impossible, and go home early.

  • Not directly, though you could convert the month number inot a date.

    SELECT DATENAME(month,'1900/' + CAST(monthNumber AS VARCHAR(2)) + '/01')

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks very much

    Warm Regards,
    Shakti Singh Dulawat
    Before printing, think about the environment
    Do the impossible, and go home early.

  • thank you GilaMonster...

  • GilaMonster (2/8/2008)


    Not directly, though you could convert the month number inot a date.

    SELECT DATENAME(month,'1900/' + CAST(monthNumber AS VARCHAR(2)) + '/01')

    Heh, too much VARCHAR in that for me, Gail... 😛

    SELECT DATENAME(mm,DATEADD(mm, MonthNumber-1,0))

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thanks a ton.....

    it helped. 😀

  • You bet... thanks for the feedback.

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • A little modification in Jeff's code.

    SELECT DateName(mm,DATEADD(mm,MonthNumber,-1))

    karthik

  • karthikeyan (3/12/2009)


    A little modification in Jeff's code.

    SELECT DateName(mm,DATEADD(mm,MonthNumber,-1))

    Yep... good call.

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Super Logic..Keep Rocking:)

  • Here is one line solution in my implementation.

    http://praveenbattula.blogspot.com/2009/04/how-to-know-month-name-from-month-index.html

  • The character conversion will slow things down quite a bit if you happen to use the for batch programming on millions of rows.

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

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • [Edit] Ack, thought I had scrolled down all the way, apparently I was wrong.

    Since I've posted anyways, might as well mention that doing it this way automatically adjusts for language settings.

    set language spanish

    select DATENAME(m,DATEADD(m,1,-1))--Enero

    select DATENAME(m,DATEADD(m,2,-1))--Febrero

    select DATENAME(m,DATEADD(m,3,-1))--Marzo

    select DATENAME(m,DATEADD(m,4,-1))--Abril

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

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

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