Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2003
    Posts
    2

    Unanswered: Convert Char to date

    I am trying to convert a char column to a datetime in a SQL query. I have tried using CAST however I get the following message:
    Syntax error converting datetime from character string

    The date part of the query looks like this:
    (CAST(date_opened_char AS datetime) >= '08/01/2003 00:00:00'

    What am I doing wrong?

    TIA!
    SV

  2. #2
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    in your date_opened_char is most probably a value (or more) that contains data that cannot be converted to a datetime value.
    By the way, if you always compare to the 0:00 time, you can omit it in the query, because sql server defaults to midnight time.
    Johan

  3. #3
    Join Date
    Aug 2003
    Posts
    2
    Below is a value that is in the column:

    05/13/02 19:20:45

  4. #4
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    if i execute this statement
    Code:
    select (CAST('05/13/02 19:20:45' AS datetime))
    then there is no problem. I am not saying that all cannot be converted, but it only takes one to receive an error. Perhaps you can create a stored procedure that browses through the data with a cursor and using the @@error variable you can determine which one(s) create(s) the error.

    Also, you have one bracket short after the 'as datetime' part, but this could be of the copy pasting.
    Last edited by jora; 08-25-03 at 17:26.
    Johan

  5. #5
    Join Date
    Dec 2002
    Location
    Antwerp, Belgium
    Posts
    227
    how do you check that the data in the char column is correct? If you don't check, a user could have mistaken with the data entry and put the '/' the other way around, like '\'
    Johan

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    use isdate(...) :

    CAST(case isdate(date_opened_char) when 0 then '01/01/1900' else date_opened_char end AS datetime) >= '08/01/2003 00:00:00'

  7. #7
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    for that date format you need

    convert(datetime, dte, 101)

    to beenvironment independant.

Posting Permissions

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