Prev: SQL Command question
Next: Convert string to uniqueidentifier?
From: Vijay via SQLMonster.com on 3 Jun 2010 08:44
Hi All,

I have a column with the rows

rtl_yearmonth
Apr 2006
Mar 2009
Jan 2010

I want to convert this data like this and insert to a int column.
200604
200903
201001

Thanks
Balaji

--
Message posted via http://www.sqlmonster.com

From: obiron via SQLMonster.com on 3 Jun 2010 08:55
cast(right(rtl_yearmonth,4) as nvarchar(4) ) +
CASE
WHEN left(rtl_yearmonth,3 = 'Jan' THEN '01'
WHEN left(rtl_yearmonth,3 = 'Feb' THEN '02'
WHEN ...
END

not sure if there is a function to convert the month short name back to its
numeric value as the monthshortname would be language dependant

Out of interest, why are you having to use a field with the date in that
format?

Obiron

Vijay wrote:
>Hi All,
>
>I have a column with the rows
>
>rtl_yearmonth
>Apr 2006
>Mar 2009
>Jan 2010
>
>I want to convert this data like this and insert to a int column.
>200604
>200903
>201001
>
>Thanks
>Balaji

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/201006/1

From: Plamen Ratchev on 3 Jun 2010 17:48
Here is one method:

SELECT CONVERT(CHAR(6), CONVERT(DATETIME, STUFF(rtl_yearmonth, 5, 0,
'01, '), 107), 112)

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: SQL Command question
Next: Convert string to uniqueidentifier?