May 9, 2004 at 1:41 am
When I run this query below I get the error message below. The problem seems to have something to do with the fact that I entered a String as the date. Both fields are "datetime" and I'm using MSSQL7.0. Is there any way to insert a string as a date or am I going to need some kind of conversion function?
QUERY:
INSERT INTO Game
(StartDateTime, endDateTime)
VALUES('Thu Apr 15 19:00:00 EDT 2004','Thu Apr 15 19:30:00 EDT 2004')
ERROR MESSAGE:
Server: Msg 241, Level 16, State 1, Line 1
Syntax error converting datetime from character string.
May 10, 2004 at 12:15 am
Kevin,
You are right. You are getting the error because you are trying to insert a string into a datetime field.
You can't do a straight convert because in your string you have things like the day (Thurs) and 'EDT'. If you had Apr 15 19:30:00 2004 you would be able to do a straight insert using CONVERT OR CAST, so you can use SUBSTRING to strip this data out.
Try inserting your string data into a Temp Table and then inserting into your datetime fields in your Game table using:
Example.
-- Create the #temp table
CREATE TABLE #GAME
(StartDateTime varchar (50),
EndDateTime varchar (50))
-- Insert your string data
INSERT INTO #GAME
(StartDateTime,
EndDateTime)
VALUES
('Thu Apr 15 19:00:00 EDT 2004','Thu Apr 15 19:30:00 EDT 2004')
-- Get the datetime data from the #temp table
INSERT INTO GAME
SELECT CAST(SUBSTRING (StartDateTime, 5, 15) + SUBSTRING (StartDateTime, 24, 29)AS Datetime) AS StartDateTime ,
CAST (SUBSTRING (EndDateTime, 5, 15) + SUBSTRING (EndDateTime, 24, 29) AS DateTime) AS EndDateTime
FROM #GAME
Hope this helps,
Cheers,
Angela
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply