| Author |
 Topic  |
|
|
ppatel112
Starting Member
19 Posts |
 Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/14/2013 : 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
19 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
1500 Posts |
Posted - 03/14/2013 : 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
Flowing Fount of Yak Knowledge
India
1419 Posts |
Posted - 03/18/2013 : 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 |
|
|
| |
Topic |
|
convert a column having 01 to january
