how to convert date month from int to varchar?

Question

i would like to ask how convert integer to month name in sql server 2005. Example My field is 01-12 , i would like to display as January - December .

anyone can help me?

Answer 1

SELECT MonthName =
  CASE month
     WHEN 1 THEN 'January'
     WHEN 2 THEN 'February'
     WHEN 3 THEN 'March'
     WHEN 4 THEN 'April'
     WHEN 5 THEN 'May'
     WHEN 6 THEN 'June'
     WHEN 7 THEN 'July'
     WHEN 8 THEN 'August'
     WHEN 9 THEN 'September'
     WHEN 10 THEN 'October'
     WHEN 11 THEN 'November'
     WHEN 12 THEN 'December'
     ELSE 'What kind of calendar is it?'
  END

See also:

• CASE (Transact-SQL)

Answer 2

In C# we can use For Full name of Month like january-December

Date.ToString("MMMM")

for jan-dec u can use

Date.ToString("MMM")

Answer 3

TO use in Stored Procedure of Sql u can use this

SELECT DATENAME(month, DATEADD(month, @mydate-1, CAST('2008-01-01' AS datetime)))

where @mydate can be 1,2,3...

Answer 4

How about this way, you create one sclar function..

CREATE FUNCTION dbo.ConvertMonthName
(
    -- Add the parameters for the function here
    @m      nvarchar(2)
)
RETURNS varchar(20)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @result varchar(20)

    DECLARE @MNO INT
    SET @MNO = CAST(@M AS INT)

    -- Add the T-SQL statements to compute the return value here
    if(@m = 1)
        SET @result = 'JANUARY'
    else if(@m = 2)
        SET @result = 'FEBRUARY'
    else if(@m = 3)
        SET @result = 'MARCH'
    else if(@m = 4)
        SET @result = 'APRIL'
    else if(@m = 5)
        SET @result = 'MAY'
    else if(@m = 6)
        SET @result = 'JUNE'
    else if(@m = 7)
        SET @result = 'JULY'
    else if(@m = 8)
        SET @result = 'AUGUST'
    else if(@m = 9)
        SET @result = 'SEPTEMBER'
    else if(@m = 10)
        SET @result = 'OCTOBER'
    else if(@m = 11)
        SET @result = 'NOVEMBER'
    else if(@m = 12)
        SET @result = 'DECEMBER'



    RETURN @result

END

-- from your sql script

DECLARE @VAR VARCHAR(5)
SET @VAR = '01-12'

SELECT dbo.ConvertMonthName(LEFT(@VAR,2)) + ' - ' + dbo.ConvertMonthName(RIGHT(@VAR,2)) [colname]

Answer 5

I'm not sure what you don't like about the other answers (what does "I prefer dynamic code" mean?) but since you are using a database, the simplest solution is to store the names in a table. Then you can easily add more columns in future if you want to support month names in multiple languages, for example.

If you do a lot of work with dates the best solution of all is to create a full calendar table (you can find plenty of discussions about them on this site) because then you can store as many names or other attributes as you like for a specific date without using any code at all.

Answer 6

CREATE FUNCTION dbo.[MonthName] (@monthNum int)
    RETURNS varchar(30)
AS
BEGIN
    RETURN
        CASE WHEN @monthNum BETWEEN 1 AND 12
            THEN DATENAME(MONTH, DATEADD(MONTH, @monthNum, '2000-12-01'))
            ELSE NULL
        END
END