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 |
|
makimark
Starting Member
34 Posts |
 Posted - 2003-11-29 : 04:59:58
|
| Help plsI have two values, one is char(8) in the format of 20031021 and the other is a char(8) in the form of 19:34:23. I need to convert these to datetime values however when i run convert(datetime,'mydate' + '' + mytime) sql doesn't like the format and returns "Syntax error converting datetime from character string". Any ideas ?thanks |
|
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2003-11-29 : 05:41:13
|
| get the input data in a format recognized by sql server..like say yyyy-mm-dd hh:mi:ss...n then use convert(datetime,string,20)..for thsi format mentioned |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-29 : 07:29:46
|
| You are missing a space(datetime,'mydate' + ' ' + mytime)xpandreyyyymmdd is better than yyyy-mm-dd as yyyy-mm-dd is ambiguous - sql server may interpret it as yyyy-dd-mm depending on settings.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
xpandre
Posting Yak Master
212 Posts |
Posted - 2003-11-29 : 08:04:31
|
| ooops..dint knew it...sorry bout that post....:-) |
|
|
|
makimark
Starting Member
34 Posts |
Posted - 2003-11-29 : 09:10:18
|
quote:
Originally posted by nr
You are missing a space(datetime,'mydate' + ' ' + mytime)xpandreyyyymmdd is better than yyyy-mm-dd as yyyy-mm-dd is ambiguous - sql server may interpret it as yyyy-dd-mm depending on settings.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Nope, had the space but still no joy -:( |
|
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2003-11-29 : 09:39:28
|
| Remove the single quotes around 'mydate', which is interpreted as a string, not the value of the field: |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-29 : 14:36:13
|
| We're all guessing. Post your code ? |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2003-11-29 : 14:40:29
|
One other possibility is that your conversion formula is correct but one or more of the rows in your source table may have an invalid date.Invalid varchar date strings can be handled like this:SELECT CASE WHEN IsDate(mydate + ' ' + mytime) = 1 THEN convert(datetime, mydate + ' ' + mytime) ELSE NULL END AS MyDatetimeFROM MyTable .... The resulting rowset has a column of DATETIME values with an occasional NULL .Sam |
|
|
|
|
|
|
DateTime Conversion