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