Converting varchar to datetime in select statement

  • How do I move a varchar string like '2004-05-13 00:00:00.000' into a datetime column when moving these strings from one table to another?

  • If necessary, use

    INSERT DestinationTable (DestField)

    SELECT CONVERT(DATETIME, MyField, 121) FROM SourceTable

    121 is not needed. It is just there for enforcing yyyy-mm-dd hh:mm:ss.mmm format. SQL Server tries to do that automatically when not present.


    N 56°04'39.16"
    E 12°55'05.25"

  • When I try that I get the following error message:

    [Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting datetime from character string. (SQL-22007)(DBD: Execute immediate failed err=-1)

    This error is in the following context:

     

    CREATE TABLE Slimmed_down_admissions ( 

     PrimDiag varchar(6) NOT NULL,   

     Spec int NOT NULL,   

     Age int NOT NULL,   

     Sex int NOT NULL,   

     OutputArea varchar(12) NOT NULL,   

     DischargeDate datetime NOT NULL,   

     EpiStartDate datetime NOT NULL,   

     EpiEndDate datetime NOT NULL,  

     PatClas int NOT NULL,   

     GP varchar(8) NOT NULL,   

     Practice varchar(8) NOT NULL

    )

     

    INSERT INTO Slimmed_down_admissions (PrimDiag, Spec, Age, Sex, OutputArea, DischargeDate, EpiStartDate, EpiEndDate, PatClas, GP, Practice)

    SELECT PrimDiag, CONVERT(INT, Spec), CONVERT(INT, Age), CONVERT(INT, Sex), OutputArea, CONVERT(DATETIME, DischargeDate), CONVERT(DATETIME, EpiStartDate), CONVERT(DATETIME, EpiEndDate), CONVERT(INT, PatClas), GP, Practice

    FROM Admissions_data A

    WHERE A.DoncResident = 'Y'

    AND A.DominantEpisode = 'Y'

    AND PrimDiag IS NOT NULL

    AND Spec IS NOT NULL

    AND Age IS NOT NULL

    AND Sex IS NOT NULL

    AND OutputArea IS NOT NULL

    AND DischargeDate IS NOT NULL

    AND EpiStartDate IS NOT NULL

    AND EpiEndDate IS NOT NULL

    AND PatClas IS NOT NULL

    AND GP IS NOT NULL

    AND Practice IS NOT NULL

    AND OutputArea LIKE '__________'

  • I think we are missing some information here. It seems that the ODBC driver does not recognize the datetime format.

    What are you trying to accomplish? Inserting rows from an outside system, into SQL Server? If so, trim the .mmm part first. If not using time either, trim that too.

    Help us helping you by giving us all relevant information.


    N 56°04'39.16"
    E 12°55'05.25"

  • Sorry, just updated my last post as you posted your new post.  I'm not sure whether I have detailed enough in the update.

  • You are using ODBC to copy from one table in SQL to another table in SQL?

    Create a stored procedure to do that for you, and call the SP from your client application. This way, there will not be any conversion errors.


    N 56°04'39.16"
    E 12°55'05.25"

  • I am using stored procedures that are activated via a ODBC connection.  The error message is what comes through to the DOS screen when I run my Perl programs.  I should have taken out that ODBC stuff.  No added confusion intended.

    Anyway, back to the problem,  I have tried to change the data types in the source table, using enterprise manage (not T-SQL).  After much playing around I have changed three of the varchar columns to datetime.  I'm not really sure how I managed to do it and consequently I am not able to change the forth column that needs changing.  So in summary, the the varchar strings in question must be recognisable as a date.  Just how I get it to recognise this I am not sure.  Maybe I need to convert it to an intermediate datatype before it works.  Can anyone offer suggestions?  The annoying thing is that I have already converted three of the columns.  I just don't know how I did it.  Maybe it is MS SQL Server 2000 issue.

  • I think one row or more in the Admissions_data table contains an invalid date. This is the most likely scenario.

    Which is the fourth column of date information? Is this column allowed to keep NULL? And the destination table is not?

    Run this code against your table!

    SELECT DischargeDate,

           ISDATE(DischargeDate),

           EpiStartDate,

           ISDATE(EpiStartDate),

           EpiEndDate,

           ISDATE(EpiEndDate),

           <fourth column>,

           ISDATE(<fourth column> )

    FROM   Admissions_data

    WHERE  ISDATE(DischargeDate) = 0

           OR ISDATE(EpiStartDate) = 0

           OR ISDATE(EpiEndDate) = 0

           OR ISDATE(<fourth column> ) = 0


    N 56°04'39.16"
    E 12°55'05.25"

  • There are some rows that return 0s in the Expr1, Expr2, Expr3 and Expr4 columns.  Does that indicate a faulty form with some of the entires?

     

     

  • Yes, the SQL Server can't interpret those days show, as valid dates for various reasons.

    One reason can be that the date format has changed, for example from d-m-y to m-d-y or any other format. Maybe there is a date 2007-02-29 which is not valid. Or dates missing at all or dates with zero as date, or month. Or maybe not an date at all. Can you post the list of invalid dates here?


    N 56°04'39.16"
    E 12°55'05.25"

  • Sorry, I didn't look closely enough at the output.  All the '0's in the output are due to NULL values.  But the string value 'NULL' and not the <NULL>.  I really need a SQL statement that will convert 'NULL' to <NULL> in every column, in every row.

  • Sorry, I didn't look closely enough at the output.  All the '0's in the output are due to NULL values.  But the string value 'NULL' and not the <NULL>.  I really need a SQL statement that will convert 'NULL' to <NULL> in every column, in every row.

  • Let us continue to use the ISDATE function as follows for generic solution

    INSERT  Slimmed_down_admissions

            (

                PrimDiag,

                Spec,

                Age,

                Sex,

                OutputArea,

                DischargeDate,

                EpiStartDate,

                EpiEndDate,

                PatClas,

                GP,

                Practice

            )

    SELECT  PrimDiag,

            CONVERT(INT, Spec),

            CONVERT(INT, Age),

            CONVERT(INT, Sex),

            OutputArea,

            CASE WHEN ISDATE(DischargeDate) = 1 THEN DischargeDate END,

            CASE WHEN ISDATE(EpiStartDate) = 1 THEN EpiStartDate END,

            CASE WHEN ISDATE(EpiEndDate) = 1 THEN EpiEndDate END,

            CONVERT(INT, PatClas),

            GP,

            Practice

    FROM    Admissions_data A

    WHERE   A.DoncResident = 'Y'

            AND A.DominantEpisode = 'Y' 

            AND PrimDiag IS NOT NULL 

            AND Spec IS NOT NULL 

            AND Age IS NOT NULL 

            AND Sex IS NOT NULL 

            AND OutputArea IS NOT NULL 

            AND DischargeDate IS NOT NULL 

            AND EpiStartDate IS NOT NULL 

            AND EpiEndDate IS NOT NULL 

            AND PatClas IS NOT NULL 

            AND GP IS NOT NULL 

            AND Practice IS NOT NULL 

            AND OutputArea LIKE '__________'

    and you should be ok. Good luck!

    Or, if you prefer a 'NULL' solution only, use following

    INSERT  Slimmed_down_admissions

            (

                PrimDiag,

                Spec,

                Age,

                Sex,

                OutputArea,

                DischargeDate,

                EpiStartDate,

                EpiEndDate,

                PatClas,

                GP,

                Practice

            )

    SELECT  PrimDiag,

            CONVERT(INT, Spec),

            CONVERT(INT, Age),

            CONVERT(INT, Sex),

            OutputArea,

            CASE WHEN ISNULL(DischargeDate, 'NULL') <> 'NULL' THEN CONVERT(DATETIME, DischargeDate) END,

            CASE WHEN ISNULL(EpiStartDate, 'NULL') <> 'NULL' THEN CONVERT(DATETIME, EpiStartDate) END,

            CASE WHEN ISNULL(EpiEndDate, 'NULL') <> 'NULL' THEN CONVERT(DATETIME, EpiEndDate) END,

            CONVERT(INT, PatClas),

            GP,

            Practice

    FROM    Admissions_data A

    WHERE   A.DoncResident = 'Y'

            AND A.DominantEpisode = 'Y' 

            AND PrimDiag IS NOT NULL 

            AND Spec IS NOT NULL 

            AND Age IS NOT NULL 

            AND Sex IS NOT NULL 

            AND OutputArea IS NOT NULL 

            AND DischargeDate IS NOT NULL 

            AND EpiStartDate IS NOT NULL 

            AND EpiEndDate IS NOT NULL 

            AND PatClas IS NOT NULL 

            AND GP IS NOT NULL 

            AND Practice IS NOT NULL 

            AND OutputArea LIKE '__________'


    N 56°04'39.16"
    E 12°55'05.25"

  • Just to note that you can use the 'NULLIF' function too (example below). But the ISDATE approach is probably safer and better.

    --data

    declare @t table (d1 varchar(30), d2 varchar(30))

    insert @t

              select '20060629', 'NULL'

    union all select 'NULL', NULL

    --calculation

    select cast(nullif(d1, 'NULL') as datetime), cast(nullif(d2, 'NULL') as datetime) from @t

    /*results

    -------------------------- ----------------------

    2006-06-29 00:00:00.000    NULL

    NULL                       NULL

    */

    Ryan Randall

    Solutions are easy. Understanding the problem, now, that's the hard part.

  • Faster too, I presume.

    Since the function ISDATE already have checked that the field is a valid date, there is no need to cast the field one more time before inserting.


    N 56°04'39.16"
    E 12°55'05.25"

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply