CODEWITHSUNDEEP
sqlsql-serverdate-formatting
For
For

Reputation: 3

Convert date to only month from the given date field

I would like to convert one of my date column to just month

I have columns values as

example : 

select datecolumn from abc

datecolumn 

jan-20
feb-20
mar-20
apr-20

I would like to get only month in the form 

01
02
03
04

Upvotes: 0

Views: 354

Answers (4)

CR7SMS
CR7SMS

Reputation: 2584

You can add a '01-' to the beginning of the field and then use DATEPART function:

DECLARE @Month char(10) = 'Feb-20'
select DATEPART(MM,'01-'+@Month)

If you want the output in two characters, then you can use the format funtion:

DECLARE @Month char(10) = 'Feb-20'
select FORMAT(DATEPART(MM,'01-'+@Month),'00')

Hope this helps.

Upvotes: 0

Dmitry
Dmitry

Reputation: 11

I would use either MONTH or DATEPART functions

SELECT DATEPART(month, '2014/04/28');

Result: 4

Upvotes: 1

Bruno Bukavu Thai
Bruno Bukavu Thai

Reputation: 141

Untested and quite sketchy but this could work:

select case when datecolumn like 'Jan%' then '01'
when datecolumn like 'Feb%' then '02'
when datecolumn like 'Mar%' then '03'
when datecolumn like 'Apr%' then '04'
when datecolumn like 'May%' then '05'
when datecolumn like 'Jun%' then '06'
when datecolumn like 'Jul%' then '07'
when datecolumn like 'Aug%' then '08'
when datecolumn like 'Sep%' then '09'
when datecolumn like 'Oct%' then '10'
when datecolumn like 'Nov%' then '11'
when datecolumn like 'Dec%' then '12'
end as datecolumn 
from abc

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269603

The data types are not correct. But you can create a new column with the format you want:

alter table t
    add datecol_mmmyy as (left(datename(month, datecol), 3) +'-' + datename(year, datecol));

Then you can refer to datecol_mmmyy and get the format you want.

Upvotes: 0

Related Questions