Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78

    Unanswered: Data Type Conversion

    I have a SQL Server 200 database and I need to change the data types in a few fields in a table.

    The fields are currently are NVARCHAR and need to be datetime

    I get an error message when trying to do this:

    ****
    - Unable to modify table.
    ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
    [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.
    ****

    My regional settings show as English UK and date format of dd/mm/yyyy.

    Can anyone help on this issue?

    Thanks in advance.

    Neil.

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245

    Re: Data Type Conversion

    The obvious suggestion (and therefore probably wrong) would be to identify rows where the nvarchar data is not actually a valid date time.

    I'm not a purist when it comes to cursors. My first approach would be to use a cursor. Scroll through the records and attempt to insert them (just the date/time column) into a new table. If you encounter an error, output sufficient data to clearly identify a row.

    Remember that it takes only a single bad data point to trash the conversion process. There may not be that many bad data points.

    Something else to remember about what SQL is doing in the background: it is actually creating a new temporary table with the date/time column, inserting the data, then dropping the old table and renaming the new temporary table to the old table name. Just food for thought.

    Regards,

    hmscott

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    My first approach would be:

    select *
    from YourTable
    where IsDate(YourTextDate) = 0

    That should show you all the rows with invalid dates.

    blindman

  4. #4
    Join Date
    Sep 2003
    Location
    Hertfordshire, England
    Posts
    78
    Thanks!

    I think I've solved the problem now.

    Some of the dates are 2003 and some are 03 so if I change the 03's to 2003 and then convert the data type it works!

    Thanks anyway!

  5. #5
    Join Date
    Dec 2002
    Posts
    1,245
    Oh, ugh. I better go back to the SQL BOL. I thought IsDate was only a VBScript function.

    Sigh. So much to learn, so little time.

    Thanks,

    hmscott

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There's a lot of functions and tools out there. I frequently see people reference usefull things on this forum that I have to go look up.

    It's too bad that when a new version comes out the help system doesn't have a section titled "Just the stuff you don't already know."

    blindman

Posting Permissions

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