Get Month Name

  • Gail

    you rock!

    but you knew that already

    this also works in SQL Server 2000

  • Similarly if you want to list all the month names for a year using a T-SQL statement, you can do this:

    SELECT Number + 1 as [MonthNumber],

    DateName(mm,DATEADD(mm,Number,0)) as [MonthName]

    FROM master..spt_values

    WHERE Type = 'P' and Number < 12

  • drop table sample_table

    create table sample_table (id int, dat datetime)

    insert into sample_table values(1,getdate())

    select * from sample_table

    select id,dat from sample_table where datename(month,dat)='June'

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

    id dat

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

    1 2010-06-07 10:54:52.607

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

    Cheers,

    Venkatesan Prabu .J

    http://venkattechnicalblog.blogspot.com/

    Thanks and Regards,
    Venkatesan Prabu, 😛
    My Blog:

    http://venkattechnicalblog.blogspot.com/

  • Venkatesan Prabu (6/6/2010)


    drop table sample_table

    create table sample_table (id int, dat datetime)

    insert into sample_table values(1,getdate())

    select * from sample_table

    select id,dat from sample_table where datename(month,dat)='June'

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

    id dat

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

    1 2010-06-07 10:54:52.607

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

    Cheers,

    Venkatesan Prabu .J

    http://venkattechnicalblog.blogspot.com/

    Ummm... I don't get it. How does that deal with the original request of converting month numbers to month names?

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

  • Awesome, thanks so much to Jeff and Karthik!

    I want to say thank you to Jeff for helping so many of us, Much appreciated!:-)

  • trishdiep (6/12/2010)


    Awesome, thanks so much to Jeff and Karthik!

    I want to say thank you to Jeff for helping so many of us, Much appreciated!:-)

    Thank you very much for the feedback. I appreciate it. I do have to say that I'm not alone in this, though. Lot's of good folks help others every day on this and many other forums. For that matter, look at how many people came up with good ideas or additional information on this thread alone. I'm humbled in their presence. 🙂

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

  • ton of Thanks

  • see link here please http://sqltosql.blogspot.com/2012/08/sql-month-name.html

  • see link here please

    http://sqltosql.blogspot.com/2012/08/sql-month-name.html

  • yanto 93637 (8/30/2012)


    see link here please

    http://sqltosql.blogspot.com/2012/08/sql-month-name.html

    Since it's in the article you posted a link for, let me show you that you don't need the complexity of a While Loop or Dynamic SQL.

    Here's the code from the article you posted...

    declare @start int,@end int

    declare @qry nvarchar(100)

    declare @summary nvarchar(1200)

    set @start=1

    set @end=13

    set @summary=''

    while not (@start=@end)

    begin

    if @start=@end-1

    set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName')qry)

    else

    set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')monthNumber, DateName(mm,DATEADD(mm,'+convert(nvarchar,@start)+',-1))monthName

    union ')qry)

    set @summary=@summary+@qry

    set @start=@start+1

    end

    exec (@summary)

    The following snippet does the same thing without the loop or the dynamic SQL.

    WITH

    cteTally AS

    (

    SELECT TOP 12

    N = ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM sys.all_columns

    )

    SELECT MonthNumber = N,

    MonthName = DATENAME(mm,DATEADD(mm,N,-1))

    FROM cteTally

    ;

    Of course, since this is an SQL Server 2000 forum, the code above won't work in SQL Server 2000. The following will, though... and, since it uses a Tally Table, the code becomes even more simple and works in all versions of SQL Server

    .

    SELECT MonthNumber = N,

    MonthName = DATENAME(mm,DATEADD(mm,N,-1))

    FROM dbo.Tally

    WHERE N <= 12

    ;

    For more information on what a Tally Table (or cteTally) is and how it can be used to replace certain loops in a very high performance fashion, please see the following article.

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

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

  • Jeff ... Thanks a lot for your explanation.

  • Many thanks for all the answers here particularly the ones from Jeff and Karthik. They enable me to get rid of a lot of CASE statements! 🙂

  • Thanks for the feedback, folks.

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

  • SELECT DATENAME(month, @MonthNumber)

    Example: SELECT DATENAME(month,1)

    Result: January

  • Sumit Rastogi (2/9/2015)


    SELECT DATENAME(month, @MonthNumber)

    Example: SELECT DATENAME(month,1)

    Result: January

    Just to note, the DATENAME functions requires a date for the second argument. They way you have it you are expressing 1900-01-01. If I do

    DATENAME(month, 31) --> 1900-01-31 it is still January.

    DATENAME(month, 32) --> 1900-02-01 it gives February.

    This method is confusing and unnecessarily complex. Please also note the age of this thread and that is has been successfully answered.

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

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

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