convert a column having 01 to january

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

convert a column having 01 to january

Author

Topic

ppatel112
Starting Member

35 Posts

Posted - 2013-03-14 : 02:08:37

Hi Guys,

i have a table which stores 01,02,03 and so on in a column CNTPRD.

how can i use an alias to convert this column data i.e. 01 to january and so on for rest of the months.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 03:39:45

DECLARE @Months TABLE ( CNTPRD varchar(2))
INSERT INTO @Months VALUES('01'), ('02'), ('03'), ('04'), ('05'), ('06'), ('07'), ('08'), ('09'), ('10'), ('11'), ('12')
SELECT DATENAME( Month ,convert(date, CNTPRD + '-01-1900')) MonthNames
FROM @Months

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-14 : 03:53:47

--Alternate for MSSQL 2012
--DATEFROMPARTS( <year>, <month>, <day> )
SELECT DATENAME( Month, DATEFROMPARTS(1900, CAST(CNTPRD AS INT),01)) MonthNames
FROM YourTable

--
Chandu

ppatel112
Starting Member

35 Posts

Posted - 2013-03-14 : 18:52:11

thanks bandi, i am using 2005 and i just need a single select statement to achieve this the sql 2012 does work fine on 2012 server

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-03-14 : 20:54:22

What Chandu posted at 03/14/2013 : 03:39:45 is a single select. He was just showing an example with a table variable. You can also use the following, which is again a single expression. If you need to localize/internationalize the strings, I would stick with Chandu's suggestion:

	case CNTPRD  
		when '01' then 'January'
		when '02' then 'February'
		when '03' then 'March'
		-- etc.
	end as MonthName

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 01:57:14

quote:
Originally posted by ppatel112

thanks bandi, i am using 2005 and i just need a single select statement to achieve this the sql 2012 does work fine on 2012 server

This is single statement
SELECT DATENAME( Month ,convert(date, CNTPRD + '-01-1900')) MonthNames

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources