Results 1 to 2 of 2
  1. #1
    Join Date
    May 2003
    Posts
    2

    Unanswered: Advanced convertion from varchar to datetime

    Hi all,

    I have to convert a varchar column to a datetime column.
    The initial column may contain any data, representing a date or not.
    The goal is to convert the varchar column to a datetime column, converting data representing a date into its equivalent and replacing data not representing a date with a specified date, say 01/01/1900.

    Here is a sample table :
    create table TEST_STRING2DATE
    (
    id int not null,
    data varchar(10) null
    )
    INSERT INTO TEST_STRING2DATE
    VALUES (0, '14/07/1989')
    INSERT INTO TEST_STRING2DATE
    VALUES (1, 'dummy')
    INSERT INTO TEST_STRING2DATE
    VALUES (2, '15/07/1989')

    I tried this :

    set dateformat dmy

    alter table TEST_STRING2DATE
    modify data datetime null

    I got an error :
    Server Message: Number 249, Severity 16
    Server 'SERVER', Line 1:
    Syntax error during explicit conversion of VARCHAR value 'dummy' to a DATETIME field.

    So i tried to use a temporary table :

    select id, convert(datetime, data, 103) data
    into TEMP_STRING2DATE
    from TEST_STRING2DATE

    But i got the same error:
    Server Message: Number 249, Severity 16
    Server 'SERVER', Line 1:
    Syntax error during explicit conversion of VARCHAR value 'dummy' to a DATETIME field.

    The convert function does not seem to work the same way when converting from char to datetime than is works when converting from float to char. In this last case, if the destination varchar column is too small to contain the representation of the float value, the convert function generates a value of '********' which is easy to detect and convert to a default value of -1 for example.

    I see no easy solution to solve the problem.
    I use Java, JDBC and JConnect 2
    Of course, i don't want to programatically load datas in memory to do the convertion myself.

    Please help !

    Thanks

  2. #2
    Join Date
    May 2003
    Posts
    2

    Re: Advanced convertion from varchar to datetime

    I've found a way to do it :

    select id, "data" =
    case
    when patindex("%/%/%", data) = 0 then '01/01/1900'
    when patindex("%/%/%", data) > 0 then convert(datetime, data, 103)
    end
    into TEMP_STRING2DATE
    from TEST_STRING2DATE

    But if anyone knows another way...

    Thanks

Posting Permissions

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