SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 convert a column having 01 to january
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ppatel112
Starting Member

26 Posts

Posted - 03/14/2013 :  02:08:37  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/14/2013 :  03:39:45  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/14/2013 :  03:53:47  Show Profile  Reply with Quote
--Alternate for MSSQL 2012
--DATEFROMPARTS( <year>, <month>, <day> )
SELECT DATENAME( Month, DATEFROMPARTS(1900, CAST(CNTPRD AS INT),01)) MonthNames
FROM YourTable

--
Chandu
Go to Top of Page

ppatel112
Starting Member

26 Posts

Posted - 03/14/2013 :  18:52:11  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3569 Posts

Posted - 03/14/2013 :  20:54:22  Show Profile  Reply with Quote
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

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/18/2013 :  01:57:14  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000