SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 DateTime Conversion
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

makimark
Starting Member

34 Posts

Posted - 11/29/2003 :  04:59:58  Show Profile  Reply with Quote
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

199 Posts

Posted - 11/29/2003 :  05:41:13  Show Profile  Reply with Quote
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
Go to Top of Page

nr
SQLTeam MVY

United Kingdom
12543 Posts

Posted - 11/29/2003 :  07:29:46  Show Profile  Visit nr's Homepage  Reply with Quote
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.
Go to Top of Page

xpandre
Posting Yak Master

199 Posts

Posted - 11/29/2003 :  08:04:31  Show Profile  Reply with Quote
ooops..
dint knew it...sorry bout that post....:-)
Go to Top of Page

makimark
Starting Member

34 Posts

Posted - 11/29/2003 :  09:10:18  Show Profile  Reply with Quote
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 -:(
Go to Top of Page

ehorn
Flowing Fount of Yak Knowledge

USA
1630 Posts

Posted - 11/29/2003 :  09:39:28  Show Profile  Reply with Quote
Remove the single quotes around 'mydate', which is interpreted as a string, not the value of the field:
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 11/29/2003 :  14:36:13  Show Profile  Reply with Quote
We're all guessing. Post your code ?
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 11/29/2003 :  14:40:29  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000