Convert date to only month from the given date field

Question

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

That isnt a date, that's clearly a varchar. You should really fix the data type, them you can use things like DATEPART. — Thom A
– Thom A ♦, Commented Feb 20, 2020 at 22:48
20 years after Y2K and people are STILL making this basic mistake! — SMor
– SMor, Commented Feb 20, 2020 at 23:09

Dale K · Accepted Answer · 2020-02-20 22:49:44Z

1

I would use either MONTH or DATEPART functions

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

Result: 4

edited Feb 20, 2020 at 22:49

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Feb 20, 2020 at 22:48

Dmitry

114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2020-02-20 22:42:39Z

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.

Bruno Bukavu Thai · Accepted Answer · 2020-02-20 22:49:33Z

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

CR7SMS · Accepted Answer · 2020-02-20 22:49:46Z

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.

Collectives™ on Stack Overflow

Convert date to only month from the given date field

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related