Post reply

Get Month Name

  • April 20, 2010 at 1:41 pm

    #1154904

    Gail

    you rock!

    but you knew that already

    this also works in SQL Server 2000

  • May 21, 2010 at 12:55 am

    #1170611

    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

  • June 6, 2010 at 6:58 pm

    #1176914

    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/

  • June 6, 2010 at 9:28 pm

    #1176925

    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)

  • June 12, 2010 at 9:07 am

    #1179541

    Awesome, thanks so much to Jeff and Karthik!

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

  • June 12, 2010 at 10:26 am

    #1179545

    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)

  • July 1, 2010 at 8:56 pm

    #1187990

    ton of Thanks

  • August 30, 2012 at 9:27 pm

    #1532191

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

  • August 30, 2012 at 9:29 pm

    #1532193

    see link here please

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

  • August 31, 2012 at 8:25 am

    #1532419

    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)

  • September 2, 2012 at 8:20 pm

    #1532799

    Jeff ... Thanks a lot for your explanation.

  • September 3, 2012 at 2:16 am

    #1532856

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

  • September 3, 2012 at 10:22 am

    #1533028

    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)

  • February 9, 2015 at 10:26 pm

    #1776535

    SELECT DATENAME(month, @MonthNumber)

    Example: SELECT DATENAME(month,1)

    Result: January

  • February 11, 2015 at 7:08 pm

    #1777125

    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