Convert month name to month number in SQL Server

Question

In T-SQL what is the best way to convert a month name into a number    E g    January  - gt  1  February  - gt  2  March  - gt  3   Etc   Are there any built in functions that can do this

User · Answer

I know this may be a bit too late but the most efficient way of doing this through a CTE as follows    WITH Months AS              SELECT 1 x        UNION all        SELECT x   1        FROM Months        WHERE x  lt  12              SELECT x AS MonthNumber  DateName  month   DateAdd  month   x   -1               AS MonthName FROM Months

User · Answer

How about this   select DATEPART MM  january 01 2011   -- returns 1 select DATEPART MM  march 01 2011    -- returns 3 select DATEPART MM  august 01 2011   -- returns 8

User · Answer

select  Convert datetime   01      Replace  OCT-12    -        6

User · Answer

There is no built in function for this   You could use a CASE statement   CASE WHEN MonthName   January  THEN 1      WHEN MonthName    February  THEN 2               WHEN MonthName    December  TNEN 12 END AS MonthNumber    or create a lookup table to join against  CREATE TABLE Months       MonthName VARCHAR 20       MonthNumber INT     INSERT INTO Months      MonthName  MonthNumber      SELECT  January   1     UNION ALL     SELECT  February   2     UNION ALL             SELECT  December   12   SELECT t MonthName  m MonthNumber     FROM YourTable t         INNER JOIN Months m             ON t MonthName   m MonthName

User · Answer

You can create a function and then refer to it in the select statement  The function may look similar to this   if OBJECT ID  fn month name to number    IF   is not null drop function fn month name to number go create function fn month name to number   monthname varchar 25   returns int as begin declare  monthno as int  select  monthno   case  monthname when  January  then 1 when  February  then 2 when  March  then 3 when  April  then 4 when  May  then 5 when  June  then 6 when  July  then 7 when  August  then 8 when  September  then 9 when  October  then 10 when  November  then 11 when  December  then 12 end return  monthno end   Then you can query it   select fn month name to number   February   as month no   This query will return 2 as month number  You can pass values from a column as parameters to the function   select fn month name to number   columnname   as month no from  tablename    Have a good day

User · Answer

You can use below code  DECLARE  T TABLE   Month  VARCHAR 20   INSERT INTO  T SELECT  January  UNION SELECT  February  UNION SELECT  March    SELECT MONTH  01-     Month     -2010   As MonthNumeric  Month  FROM  T ORDER BY MonthNumeric

User · Answer

I recently had a similar experience  sql server 2012   I did not have the luxury of controlling the input  I just had a requirement to report on it  Luckily the dates were entered with leading 3 character alpha month abbreviations  so this made it simple  amp  quick     TRY CONVERT DATETIME REPLACE obs DateValueText SUBSTRING obs DateValueText 1 3  CHARINDEX SUBSTRING obs DateValueText 1 3      JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC   4      It worked for 12 hour      Feb-14-2015 5 00 00 PM  2015-02-14 17 00 00 000 and 24 hour times      Sep-27-2013 22 45   2013-09-27 22 45 00 000   thanks ryanyuyu

User · Answer

You can try sth like this  if you have month name which is string datetype After converting  you can feel free to order by Month   For example  your table like this     month  Dec  Jan  Feb  Nov  Mar               My syntax is    Month cast month  1 2016  as datetime

User · Answer

Its quit simple  Take the first 3 digits of the month name and use this formula   Select charindex  DEC   JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC   4 1

User · Answer

How about this   SELECT MONTH  March      1 2014      Would return 3

User · Answer

SELECT DATEPART MM  january    01 1900   SELECT MONTH  january      01 1900   SELECT month dateadd month DATEDIFF month 0  january 01 2015   0

User · Answer

You can do it this way  if you have the date  e g  SubmittedDate   DATENAME MONTH DATEADD MONTH  MONTH SubmittedDate  - 1  0   AS ColumnDisplayMonth   Or you can do it this way  if you have the month as an int  DATENAME MONTH DATEADD MONTH   monthInt - 1  0   AS ColumnDisplayMonth

User · Answer

I think you may even have a separate table like a monthdetails  Monthno int  monthnames char 15   and include values   1 January 2 February         and so on  and then join this table with your existing table in the monthnames column  SELECT t1   t2 Monthno from table1  left outer join monthdetails t2 on t1 monthname t2 monthnames order by t2 Monthno

[sql-server] Convert month name to month number in SQL Server

Examples related to sql-server