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 |
|
jtwork
Yak Posting Veteran
82 Posts |
 Posted - 2007-11-06 : 10:03:40
|
| i have a number field yyyymmdd which i would like to convert to a date.Please help? |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-11-06 : 10:09:04
|
[code]convert(datetime, convert(varchar(10), number_field))[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
jtwork
Yak Posting Veteran
82 Posts |
Posted - 2007-11-06 : 10:14:34
|
| for some reason i am gettingSyntax error converting datetime from character string. |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-06 : 10:28:57
|
| Post your exact code and the exact error message... |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-11-06 : 10:32:14
|
| Please verify that all data in number column confirms to the said format and contains valid datetime data.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-06 : 10:41:40
|
Are all dates valid?Check to see if you have "dates" like these20070229, 20070230, 2007023120071477
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Van
Constraint Violating Yak Guru
462 Posts |
Posted - 2007-11-06 : 10:51:26
|
| Yep, that was gonna be my next question after they posted the code and error. |
|
|
|
jtwork
Yak Posting Veteran
82 Posts |
Posted - 2007-11-06 : 11:30:22
|
| yes i had a few 0 in the date field so i put together a case statement to only convert where not 0many thanks, |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2007-11-07 : 00:48:32
|
| More reliable methodSelect convert(datetime, convert(varchar(8), number_field)) from tablewhere ISDATE(number_field)=1 and LEN(number_field)=8MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
number to date