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.
| Author |
Topic |
|
craigwg
Posting Yak Master
154 Posts |
 Posted - 2010-12-17 : 16:46:52
|
| I have a column that contains an nvarchar value equal to 'January 2010', 'August 2010', and so on.I need to create a column that is a date equal to '2010-01-01', '2010-08-01' and so on.I have tried all combinations of casting and converting and charindex and I can't get it. Please help!Craig Greenwood |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-12-17 : 16:59:30
|
| Here you go:--this is just to show you how to do it, you can instead use a table/columndeclare @s varchar(50)--sample:set @s = 'January 2010' -- 'August 2010'--just to show you how I got it to a date format that we can then play with:SELECT LEFT(@s, CHARINDEX(' ', @s)-1) + ' 01 ' + SUBSTRING(@s, CHARINDEX(' ', @s)+1, DATALENGTH(@s))--provides your requested output:SELECT REPLACE(CONVERT(char(10), CONVERT(datetime, LEFT(@s, CHARINDEX(' ', @s)-1) + ' 01 ' + SUBSTRING(@s, CHARINDEX(' ', @s)+1, DATALENGTH(@s))), 111), '/', '-')Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-12-20 : 05:57:16
|
| 1 Always use proper DATETIME datatype to store dates2 Another method is select cast(@s as datetime)MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
|
|
converting monthname to a date