Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Location
    Connecticut
    Posts
    4

    Unanswered: Syntax error converting datetime from character string

    I am using INSERT to replace about 160 fields for one company in the company table, all goes well until I come to the Rn_edit_date field which is a datetime datatype. When I insert the value of:

    cast('2005-03-15 09:25:44.463' as datetime)

    Error message as a result is: Syntax error converting datetime from character string.

    NOTE: 2005-03-15 09:25:44.463 is the correct format which all other companies abide by, so I'm totally lost at where I'm going wrong?

    Any help would be appreciated

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    That expression works nicely on my machine. I'm betting you have a different expresssion in your statement that is causing the problem. Just for the jolly factor, try to run:
    Code:
    SELECT cast('2005-03-15 09:25:44.463' as datetime)
    on your machine. I'd bet it runs perfectly, which would imply that whatever problem you're having is somewhere else.

    -PatP

  3. #3
    Join Date
    Sep 2005
    Location
    Connecticut
    Posts
    4
    Thanx Pat,

    The select runs perfectly fine, returning all rows for all companies, but I'm still missing something. Is there a way I can simply bypass updating info to this field?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If your schema permits NULL values for the date column, just remove all reference to that column from the INSERT statement's column list and the SELECT list too. If you can't have NULL values, then I'd just use the constant of '2005-03-15 09:25:44.463' and avoid the explicit conversion.

    -PatP

  5. #5
    Join Date
    Oct 2010
    Posts
    1

    Syntax error converting datetime from character string

    cast('2005-03-15 09:25:44.463' as datetime)

    just remove the .463 if you are using cast

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Best to use a language independent format. The below will run in any language on any SQL Server:
    Code:
    SELECT CAST('2005-03-15T09:25:44.463' AS DATETIME)
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •