Question About Inserting A String as A Date

• Kevin-138583

  SSC Enthusiast

  Points: 196

  More actions

  May 9, 2004 at 1:41 am

  #87143

  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.

• Angela But

  Hall of Fame

  Points: 3848

  More actions

  May 10, 2004 at 12:15 am

  #505746

  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