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 |
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
 Posted - 2007-05-17 : 04:35:22
|
| I get a Flat file with date format as 070516 in col007 Move it to a temp table into a varchar datatype col named old_dateI Update new_date column of data type datetime based on the old_date varchar datatypeUPDATE tbl_newSET new_date = convert(datetime,right(Col007,2) + '-'+ substring(Col007, 3,2) +'-'+ left(Col007,2))this is the error am getting Move fromError string: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.Any help would be great to get the string date into a datatype of datetime |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2007-05-17 : 04:39:04
|
tryconvert(datetime, col007, 12)
KH |
 |
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-05-17 : 04:55:20
|
| Thats returning a error also for me..this is what i used UPDATE tbl_newSET new_date = convert(datetime,right(Col007,2) + '-'+ substring(Col007, 3,2) +'-'+ left(Col007,2),12) |
|
|
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2007-05-17 : 05:01:57
|
| No need to use substring etcuse thisUPDATE tbl_newSET new_date = convert(datetime,Col007,12) |
|
|
|
rookie_sql
Constraint Violating Yak Guru
443 Posts |
Posted - 2007-05-17 : 05:20:37
|
| Yep that worked, the funny thing about the old script it did work for about 2 weeks and did work when i ran the job manually from the dts package but when i scheduled the job it failed.. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
date in string format