| Author |
 Topic  |
|
|
makimark
Starting Member
34 Posts |
 Posted - 11/29/2003 : 04:59:58
|
Help pls
I 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
193 Posts |
Posted - 11/29/2003 : 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
United Kingdom
12543 Posts |
Posted - 11/29/2003 : 07:29:46
|
You are missing a space
(datetime,'mydate' + ' ' + mytime)
xpandre
yyyymmdd 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
193 Posts |
Posted - 11/29/2003 : 08:04:31
|
ooops..
dint knew it...sorry bout that post....:-)
|
|
|
|
makimark
Starting Member
34 Posts |
Posted - 11/29/2003 : 09:10:18
|
quote:
Originally posted by nr
You are missing a space
(datetime,'mydate' + ' ' + mytime)
xpandre
yyyymmdd 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
Flowing Fount of Yak Knowledge
USA
1629 Posts |
Posted - 11/29/2003 : 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
USA
3459 Posts |
Posted - 11/29/2003 : 14:36:13
|
| We're all guessing. Post your code ? |
|
|
|
SamC
White Water Yakist
USA
3459 Posts |
Posted - 11/29/2003 : 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 MyDatetime
FROM MyTable ....
The resulting rowset has a column of DATETIME values with an occasional NULL .
Sam |
Edited by - SamC on 11/29/2003 14:51:22 |
|
|
| |
Topic |
|
DateTime Conversion
