CODEWITHSUNDEEP
sqlsql-servert-sqldateselect
Sreelakshmi narayanan
Sreelakshmi narayanan

Reputation: 39

Converting month name to month number in SQL Server

I have a column (says Period_name ) that accepts varchar value. Inside the column, I have inputs likeJan-19, Feb-19, etc. I need an SQL instruction whenever left of Period_name comes like Jan, Feb then it should convert into corresponding month number. For example

Input

Period_name
Jan-19
Feb-19

Output

Period name 
01
02

The SQL query condition is as follows:

Case 
When Left(period_name,3) in{'Jan','Feb'} Then Month(Left(period_name,3))
END

Thanks :)

Upvotes: 2

Views: 5995

Answers (9)

John Cappelletti
John Cappelletti

Reputation: 82010

Just another option is to try_convert() into a date

Note: the format() is optional

Example

Declare @YourTable Table ([Period_Name] varchar(50))  Insert Into @YourTable Values 
 ('Jan-19')
,('Feb-19')

Select * 
      ,NewVal = format(try_convert(date,'01-'+Period_Name),'MM')
 from @YourTable

Returns

Period_Name NewVal
Jan-19      01
Feb-19      02

Upvotes: 2

lptr
lptr

Reputation: 6808

PARSE/TRY_PARSE:

select *, 
month(try_parse(v.val as date)), 
month(try_parse(concat(left(v.val+'-', charindex('-', v.val+'-')), '01') as date))
from 
(
values('Jan-19'),('Feb-20'),('Mar-21'),('Apr-03'),('May-18'),('Jun-99'),('Jul-85'),
('Aug-15'), ('Sep-16'),('Oct-'),('Nov'), ('Dec-11')
) as v(val);

Upvotes: 0

OO7
OO7

Reputation: 690

A simply approach:

select right(t.period_name, 2) year, m.period, m.name
from
(select '01' period, 'Jan' name
  union
  select '02', 'Feb'
   ...
  union
  select '12', 'Dec') m
inner join table1 t
   on Left(t.period_name,3) = m.name
order by right(t.period_name, 2), m.period

Return:

year -- period -- name
19 ----- 01 -------- Jan
19 ----- 02 -------- Feb
19 ----- 03 -------- Mar
 ...
19 ----- 12 -------- Dec
20 ----- 01 -------- Jan
20 ----- 02 -------- Feb
20 ----- 03 -------- Mar
 ...
20 ----- 12 -------- Dec
 ...

Upvotes: 0

Suraj Kumar
Suraj Kumar

Reputation: 5653

There are several methods available one of that is as shown below. The logic is to convert the Period_Name into a date and use the SQL Server DATEPART() Function.

You can use the TRY_CAST (Transact-SQL) function to handle the error when the part_name value is not in the expected format. It will return null in that case.

SELECT DATEPART(MM,  + '01-' + 'Jan-19') as MonthNumber

Declare @YourTable Table ([Period_Name] varchar(50))  Insert Into @YourTable Values 
 ('Jan-19')
,('Feb-19')

SELECT DATEPART(MM,  + Try_cast('01-' + Period_Name as Date)) as MonthNumber
FROM @YourTable

Here is the live db<>fiddle demo.

To get the number in double-digit you can try the following query. Note, in this case, you need to convert the number as a string.

SELECT 
    RIGHT('0' + CAST(DATEPART(MM,  + '01-' + Period_Name)  AS VARCHAR(2)), 2) as MonthNumber
FROM @YourTable

Upvotes: 0

Thiyagu
Thiyagu

Reputation: 1340

Try this:

SELECT LEFT(CONVERT(VARCHAR,CAST('01-'+Column_Name AS DATE),1),2) ,*
FROM Table_Name

This Code Works for your Case. 1st we Should convert our varchar Column into date data type by concating Default date as 01 and then we should use convert function to return for your data type.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453910

SELECT shortmonths
FROM sys.syslanguages
WHERE langid = 0

Returns 

Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec

So another option would be to do a substring look up on that...

SELECT 1 + NULLIF(CHARINDEX(LEFT(y.period_name,3), l.shortmonths),0)/4
FROM dbo.YourTable y
CROSS JOIN sys.syslanguages l 
WHERE l.langid = 0

Upvotes: 3

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use datepart() :

SELECT RIGHT(CONCAT('00', DATEPART(MONTH, CONVERT(DATE, CONCAT('01-', period_name)))), 2);

If you don't want 0s then only datepart() would find :

SELECT DATEPART(MONTH, CONVERT(DATE, CONCAT('01-', period_name)));

Upvotes: 0

forpas
forpas

Reputation: 164204

You can use the functions convert() and format() like this: 

select format(convert(date, replace(Period_name, '-', ' 01 '), 1), 'MM-yy') result
from tablename

See the demo
Results:

> | result |
> | :----- |
> | 01-19  |
> | 02-19  |

Upvotes: 0

GMB
GMB

Reputation: 222682

You could use a case expression:

case left(period_name, 3)
    when 'Jan' then 1
    when 'Feb' then 2
    when 'Mar' then 3
    ...
    when 'Dec' then 12
end month_number

But better yet, you can take advantage of SQL Server's date parsing flexibilty. Something like '01-Jan-19' should be recognized as a date, so this should work:

month(cast('01-' + period_name as date)) month_number

Demo on DB Fiddle:

select period_name, month(cast('01-' + period_name as date)) month_number
from (values ('Jan-19'), ('Feb-19')) t(period_name)

GO

period_name | month_number
:---------- | -----------:
Jan-19      |            1
Feb-19      |            2

Upvotes: 0

Related Questions