Convert Month Number to Month Name Function in SQL

Question

I have months stored in SQL Server as 1 2 3 4    12  I would like to display them as January February etc  Is there a function in SQL Server like MonthName 1    January  I am trying to avoid a CASE statement  if possible

User · Answer

to char to date V MONTH NUM  MM    MONTH     where V MONTH NUM is the month number  SELECT to char to date V MONTH NUM  MM    MONTH    from dual

User · Answer

Use this statement to convert Month numeric value to Month name   SELECT CONVERT CHAR 3   DATENAME MONTH  GETDATE

User · Answer

In some locales like Hebrew  there are leap months dependant upon the year so to avoid errors in such locales you might consider the following solution   SELECT DATENAME month  STR YEAR GETDATE     4    REPLACE STR  month  2         0      01

User · Answer

SUBSTRING  JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC      intMonth   4  - 3  3

User · Answer

to char to date V MONTH NUM  MM    MONTH     where V MONTH NUM is the month number  SELECT to char to date V MONTH NUM  MM    MONTH    from dual

User · Answer

The following works for me   CAST GETDATE   AS CHAR 3

User · Answer

In some locales like Hebrew  there are leap months dependant upon the year so to avoid errors in such locales you might consider the following solution   SELECT DATENAME month  STR YEAR GETDATE     4    REPLACE STR  month  2         0      01

User · Answer

Working for me  SELECT MONTHNAME  lt fieldname gt   AS  Month Name  FROM  lt tablename gt  WHERE  lt condition gt

User · Answer

The easiest way is by calling the function MONTHNAME your date   your date can be a static value or the value from one of your table fields

User · Answer

you can get the date like this   eg - Users table  id name created at 1  abc  2017-09-16 2  xyz  2017-06-10   you can get the monthname like this  select year created at   monthname created at  from users    output   ----------- -------------------------------    year created at    monthname created at      ----------- -------------------------------         2017          september                       2017          june

User · Answer

Just subtract the current month from today s date  then add back your month number   Then use the datename function to give the full name all in 1 line   print datename month dateadd month -month getdate      9 getdate

User · Answer

To convert month number to month name  try the below  declare  month smallint   1 select DateName mm DATEADD mm  month - 1 0

User · Answer

SELECT DATENAME MONTH dateadd month  -3 getdate

User · Answer

Here is my solution using some information from others to solve a problem   datename month dateadd month datepart month Help HelpMain Ticket Closed Date  -1   as monthname

User · Answer

Sure this will work  select datename M GETDATE

User · Answer

SELECT DATENAME MONTH dateadd month  -3 getdate

User · Answer

Try this  SELECT MONTHNAME concat  1970-   Month int val   -01    For example- SELECT MONTHNAME concat  1970-  4  -01    The answer is - April

User · Answer

SUBSTRING  JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC      intMonth   4  - 3  3

User · Answer

You can use the convert functin as below  CONVERT VARCHAR 3   DATENAME MM  GETDATE     100

User · Answer

select monthname curdate       OR  select monthname  2013-12-12

User · Answer

Just subtract the current month from today s date  then add back your month number   Then use the datename function to give the full name all in 1 line   print datename month dateadd month -month getdate      9 getdate

User · Answer

Sure this will work  select datename M GETDATE

User · Answer

You can use the convert functin as below  CONVERT VARCHAR 3   DATENAME MM  GETDATE     100

User · Answer

There is no system defined function in SQL server  But you can create your own user-defined function- a scalar function  You would find scalar functions in the Object Explorer for your database  Programmability- Functions- Scalar-valued Functions  Below  I use a table variable to bring it all together   --Create the user-defined function CREATE FUNCTION getmonth   num int  RETURNS varchar 9  --since  September  is the longest string  length 9 AS BEGIN  DECLARE  intMonth Table  num int PRIMARY KEY IDENTITY 1 1   month varchar 9    INSERT INTO  intMonth VALUES   January      February      March      April      May                                  June      July      August      September      October                                  November      December    RETURN  SELECT I month         FROM  intMonth I         WHERE I num    num  END GO  --Use the function for various months SELECT dbo getmonth 4  AS  Month  SELECT dbo getmonth 5  AS  Month  SELECT dbo getmonth 6  AS  Month

User · Answer

I think this is the best way to get the month name when you have the month number  Select DateName  month   DateAdd  month    MonthNumber   0   - 1     Or   Select DateName  month   DateAdd  month    MonthNumber   -1

User · Answer

in addition to original    SELECT DATENAME m  str 2      1 2011      you can do this    SELECT DATENAME m  str  column name       1 2011      this way you get names for all rows in a table  where  column name  represents a integer column containing numeric value 1 through 12  2 represents any integer  by contact string i created a date where i can extract the month    1 2011  can be any date   if you want to do this with variable  DECLARE  integer int   SET  integer   6   SELECT DATENAME m  str  integer      1 2011

User · Answer

The easiest way is by calling the function MONTHNAME your date   your date can be a static value or the value from one of your table fields

User · Answer

i think this is enough to get month name when u have date   SELECT DATENAME month  GETDATE

User · Answer

I think this is the best way to get the month name when you have the month number  Select DateName  month   DateAdd  month    MonthNumber   0   - 1     Or   Select DateName  month   DateAdd  month    MonthNumber   -1

User · Answer

A little hacky but should work   SELECT DATENAME month  DATEADD month   mydate-1  CAST  2008-01-01  AS datetime

User · Answer

Starting with SQL Server 2012  you can use FORMAT and DATEFROMPARTS to solve this problem    If you want month names from other cultures  change  en-US   select FORMAT DATEFROMPARTS 1900   month num  1    MMMM    en-US     If you want a three-letter month   select FORMAT DATEFROMPARTS 1900   month num  1    MMM    en-US     If you really want to  you can create a function for this   CREATE FUNCTION fn month num to name        month num tinyint   RETURNS varchar 20  AS BEGIN     RETURN FORMAT DATEFROMPARTS 1900   month num  1    MMMM    en-US   END

User · Answer

You can use  the inbuilt CONVERT function  select CONVERT varchar 3   Date  100   as Month from MyTable    This will display first 3 characters of month  JAN FEB etc

User · Answer

select monthname curdate       OR  select monthname  2013-12-12

User · Answer

Use this statement for getting month name   DECLARE  date datetime SET  date  2015 1 4 00 00 00   SELECT CAST DATENAME month  date    AS CHAR 3  AS  Month Name    This will give you short month name  Like this  Jan  Feb  Mar  etc

User · Answer

you can get the date like this   eg - Users table  id name created at 1  abc  2017-09-16 2  xyz  2017-06-10   you can get the monthname like this  select year created at   monthname created at  from users    output   ----------- -------------------------------    year created at    monthname created at      ----------- -------------------------------         2017          september                       2017          june

User · Answer

A little hacky but should work   SELECT DATENAME month  DATEADD month   mydate-1  CAST  2008-01-01  AS datetime

User · Answer

Use this statement to convert Month numeric value to Month name   SELECT CONVERT CHAR 3   DATENAME MONTH  GETDATE

User · Answer

Declare  MonthNumber int SET  MonthNumber DatePart Month GETDATE    Select DateName  month   DateAdd  month    MonthNumber   0   - 1     Explaination    First Decalre Variable MonthNumber Get Current Month for DatePart which Return Month Number Third Query Return Month Name

User · Answer

SELECT DATENAME month  GETDATE    AS  Month Name

User · Answer

It is very simple   select DATENAME month  getdate      output   January

User · Answer

You can create a function like this to generate the Month and do SELECT dbo fn GetMonthFromDate date column  as Month FROM table name          Object   UserDefinedFunction  dbo   fn GetMonthFromDate     Script Date  11 16 2018 10 26 33 AM         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE FUNCTION  dbo   fn GetMonthFromDate     date datetime  RETURNS varchar 50  AS BEGIN     DECLARE  monthPart int  SET  monthPart   MONTH  date   IF  monthPart   1     BEGIN         RETURN  January      END ELSE IF  monthPart   2     BEGIN         RETURN  February      END ELSE IF  monthPart   3     BEGIN         RETURN  March      END ELSE IF  monthPart   4     BEGIN         RETURN  April      END ELSE IF  monthPart   5     BEGIN         RETURN  May      END ELSE IF  monthPart   6     BEGIN         RETURN  June      END ELSE IF  monthPart   7     BEGIN         RETURN  July      END ELSE IF  monthPart   8     BEGIN         RETURN  August      END ELSE IF  monthPart   9     BEGIN         RETURN  September      END ELSE IF  monthPart   10     BEGIN         RETURN  October      END ELSE IF  monthPart   11     BEGIN         RETURN  November      END ELSE IF  monthPart   12     BEGIN         RETURN  December      END RETURN NULL END

User · Answer

You can use  the inbuilt CONVERT function  select CONVERT varchar 3   Date  100   as Month from MyTable    This will display first 3 characters of month  JAN FEB etc

User · Answer

Use this statement  SELECT TO CHAR current date  dd MONTH yyyy   FROM dual   this will convert the month number to month full string

User · Answer

Starting with SQL Server 2012  you can use FORMAT and DATEFROMPARTS to solve this problem    If you want month names from other cultures  change  en-US   select FORMAT DATEFROMPARTS 1900   month num  1    MMMM    en-US     If you want a three-letter month   select FORMAT DATEFROMPARTS 1900   month num  1    MMM    en-US     If you really want to  you can create a function for this   CREATE FUNCTION fn month num to name        month num tinyint   RETURNS varchar 20  AS BEGIN     RETURN FORMAT DATEFROMPARTS 1900   month num  1    MMMM    en-US   END

User · Answer

in addition to original    SELECT DATENAME m  str 2      1 2011      you can do this    SELECT DATENAME m  str  column name       1 2011      this way you get names for all rows in a table  where  column name  represents a integer column containing numeric value 1 through 12  2 represents any integer  by contact string i created a date where i can extract the month    1 2011  can be any date   if you want to do this with variable  DECLARE  integer int   SET  integer   6   SELECT DATENAME m  str  integer      1 2011

User · Answer

SELECT DateName M  DateAdd M   MONTHNUMBER  -1

User · Answer

Declare  MonthNumber int SET  MonthNumber DatePart Month GETDATE    Select DateName  month   DateAdd  month    MonthNumber   0   - 1     Explaination    First Decalre Variable MonthNumber Get Current Month for DatePart which Return Month Number Third Query Return Month Name

User · Answer

I think this is the best way to get the month name when you have the month number  Select DateName  month   DateAdd  month    MonthNumber   0   - 1     Or   Select DateName  month   DateAdd  month    MonthNumber   -1

User · Answer

Use this statement  SELECT TO CHAR current date  dd MONTH yyyy   FROM dual   this will convert the month number to month full string

User · Answer

A little hacky but should work   SELECT DATENAME month  DATEADD month   mydate-1  CAST  2008-01-01  AS datetime

User · Answer

SELECT DATENAME month  GETDATE    AS  Month Name

User · Answer

It is very simple   select DATENAME month  getdate      output   January

User · Answer

Use the Best way   Select DateName  month   DateAdd  month    MonthNumber   -1

User · Answer

This one worked for me     MetricMonthNumber  some number   SELECT   DateName  month   DateAdd  month    MetricMonthNumber - 1    1900-01-01       AS MetricMonthName FROM TableName   From a post above from  leoinfo and  Valentino Vranken   Just did a quick select and it works

User · Answer

To convert month number to month name  try the below  declare  month smallint   1 select DateName mm DATEADD mm  month - 1 0

User · Answer

Try this  SELECT MONTHNAME concat  1970-   Month int val   -01    For example- SELECT MONTHNAME concat  1970-  4  -01    The answer is - April

User · Answer

You can create a function like this to generate the Month and do SELECT dbo fn GetMonthFromDate date column  as Month FROM table name          Object   UserDefinedFunction  dbo   fn GetMonthFromDate     Script Date  11 16 2018 10 26 33 AM         SET ANSI NULLS ON GO SET QUOTED IDENTIFIER ON GO CREATE FUNCTION  dbo   fn GetMonthFromDate     date datetime  RETURNS varchar 50  AS BEGIN     DECLARE  monthPart int  SET  monthPart   MONTH  date   IF  monthPart   1     BEGIN         RETURN  January      END ELSE IF  monthPart   2     BEGIN         RETURN  February      END ELSE IF  monthPart   3     BEGIN         RETURN  March      END ELSE IF  monthPart   4     BEGIN         RETURN  April      END ELSE IF  monthPart   5     BEGIN         RETURN  May      END ELSE IF  monthPart   6     BEGIN         RETURN  June      END ELSE IF  monthPart   7     BEGIN         RETURN  July      END ELSE IF  monthPart   8     BEGIN         RETURN  August      END ELSE IF  monthPart   9     BEGIN         RETURN  September      END ELSE IF  monthPart   10     BEGIN         RETURN  October      END ELSE IF  monthPart   11     BEGIN         RETURN  November      END ELSE IF  monthPart   12     BEGIN         RETURN  December      END RETURN NULL END

User · Answer

In some locales like Hebrew  there are leap months dependant upon the year so to avoid errors in such locales you might consider the following solution   SELECT DATENAME month  STR YEAR GETDATE     4    REPLACE STR  month  2         0      01

User · Answer

This one worked for me     MetricMonthNumber  some number   SELECT   DateName  month   DateAdd  month    MetricMonthNumber - 1    1900-01-01       AS MetricMonthName FROM TableName   From a post above from  leoinfo and  Valentino Vranken   Just did a quick select and it works

User · Answer

A little hacky but should work   SELECT DATENAME month  DATEADD month   mydate-1  CAST  2008-01-01  AS datetime

User · Answer

In some locales like Hebrew  there are leap months dependant upon the year so to avoid errors in such locales you might consider the following solution   SELECT DATENAME month  STR YEAR GETDATE     4    REPLACE STR  month  2         0      01

User · Answer

i think this is enough to get month name when u have date   SELECT DATENAME month  GETDATE

User · Answer

SELECT DateName M  DateAdd M   MONTHNUMBER  -1

User · Answer

Use the Best way   Select DateName  month   DateAdd  month    MonthNumber   -1

User · Answer

The following works for me   CAST GETDATE   AS CHAR 3

User · Answer

Here is my solution using some information from others to solve a problem   datename month dateadd month datepart month Help HelpMain Ticket Closed Date  -1   as monthname

User · Answer

Use this statement for getting month name   DECLARE  date datetime SET  date  2015 1 4 00 00 00   SELECT CAST DATENAME month  date    AS CHAR 3  AS  Month Name    This will give you short month name  Like this  Jan  Feb  Mar  etc

User · Answer

There is no system defined function in SQL server  But you can create your own user-defined function- a scalar function  You would find scalar functions in the Object Explorer for your database  Programmability- Functions- Scalar-valued Functions  Below  I use a table variable to bring it all together   --Create the user-defined function CREATE FUNCTION getmonth   num int  RETURNS varchar 9  --since  September  is the longest string  length 9 AS BEGIN  DECLARE  intMonth Table  num int PRIMARY KEY IDENTITY 1 1   month varchar 9    INSERT INTO  intMonth VALUES   January      February      March      April      May                                  June      July      August      September      October                                  November      December    RETURN  SELECT I month         FROM  intMonth I         WHERE I num    num  END GO  --Use the function for various months SELECT dbo getmonth 4  AS  Month  SELECT dbo getmonth 5  AS  Month  SELECT dbo getmonth 6  AS  Month

User · Answer

I think this is the best way to get the month name when you have the month number  Select DateName  month   DateAdd  month    MonthNumber   0   - 1     Or   Select DateName  month   DateAdd  month    MonthNumber   -1

User · Answer

Working for me  SELECT MONTHNAME  lt fieldname gt   AS  Month Name  FROM  lt tablename gt  WHERE  lt condition gt

[sql] Convert Month Number to Month Name Function in SQL

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to sql-server-2005