none
Conversion failed when converting date and/or time from character string

    Pregunta

  • I'm not sure if this is the correct forum to post this in, but I am using SSIS 2008 and I encounter the error below when I run this package.

    It fails on this piece of code:

     

    DELETE   FROM rd_information3_echo
    where client_id in ('155677','179476')
    
    DELETE   FROM rd_information3_echo
    where client_id not in ('105225','105360')
    
    ;with Numbered as (
         select client_id,client_information_id,last_name,first_name,middle_name,agency_id_no,gender,gender_code,ss_number,date_of_birth,street_address_1,street_address_2,
         City,[state],zip_code,religion,religion_code,ethnicity,ethnicity_code,race_1,race_1_code,original_table_name,ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY client_information_id) rn
         from rd_information3_echo
    )
    insert into rd_information3_cleaned
    select * 
    from Numbered where rn=1
    
    

    Out of these fields, the only DATE field is date_of_birth.  So I tried casting it as a DATETIME just to be sure, but I still got the same error.  And the code above I entered into the SQL portion of an Execute SQL Task.  I get this same error below running it in SSMS 2008.  And I even commented out the date_of_birth field, but I still seemed to have the same problem.

    Information: 0x4004300B at Write to rd_information3_echo, SSIS.Pipeline: "component "Destination - rd_information3_echo" (67)" wrote 569 rows.
    Information: 0x40043009 at Write to rd_information3_echo, SSIS.Pipeline: Cleanup phase is beginning.
    Error: 0xC002F210 at clean rd_information3_echo table, Execute SQL Task: Executing the query "DELETE   FROM rd_information3_echo
    where client_id..." failed with the following error: "Conversion failed when converting date and/or time from character string.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
    Task failed: clean rd_information3_echo table
    SSIS package "Echo Information Migration2.dtsx" finished: Failure.
    


    and this is the code that I use to form the rd_information3_echo table:

    select distinct
       --en.location_c as Location, --This only exists to identify data errors by location and does not run when loading data.
       CONVERT(nvarchar(20),c.clientcode_c) AS client_id, --Legacy CDT# (must be the same legacy ID for all conversion tables)
       newid() as client_information_id,
       CONVERT(nvarchar(50),c.lastname_vc) AS last_name,
       CONVERT(nvarchar(50),c.firstname_vc) AS first_name,
       CONVERT(nvarchar(15),c.middlename_vc) AS middle_name,
       CONVERT(nvarchar(20),c.altclientcode_vc) AS agency_id_no, --TNKIDS Number
       CONVERT(nvarchar(50),
          CASE
          WHEN c.gender_c = 'M' THEN 'Male'
          WHEN c.gender_c = 'F' THEN 'Female'
          WHEN c.gender_c = 'U' THEN 'Unknown'
          ELSE 'Unknown'
          END) AS gender,    
       CONVERT(varchar(20),
          CASE
          WHEN c.gender_c = 'M' THEN 'M'
          WHEN c.gender_c = 'F' THEN 'F'
          WHEN c.gender_c = 'U' THEN 'U'
          Else 'U'      
          END ) AS gender_code,
       CONVERT(nvarchar(9),(SUBSTRING(c.socialsecnum_c, 1,3)+SUBSTRING(c.socialsecnum_c, 5,2)+SUBSTRING(c.socialsecnum_c, 8,4))) AS ss_number,
         c.birthdate_d AS date_of_birth,
       CONVERT(nvarchar(50), address1_vc) AS street_address_1,
       CONVERT(nvarchar(50), address2_vc) AS street_address_2,
       CONVERT(nvarchar(50), city_vc) AS City,
       CONVERT(nvarchar(50), ad.[state]) AS state,
       CONVERT(nvarchar(2), ad.[state]) AS state_code,
       CONVERT(nvarchar(9), zip_c) AS zip_code,
       CONVERT(nvarchar(50),
          CASE
          WHEN c.religion_c = 'A' THEN '7th Day Adventist'
          WHEN c.religion_c = 'AG' THEN 'Agnostic'
          WHEN c.religion_c = 'AT' THEN 'Atheist'
          WHEN c.religion_c = 'B' THEN ' Buddhist'
          WHEN c.religion_c = 'BA' THEN 'Baptist'
          WHEN c.religion_c = 'C' THEN 'Catholic'
          WHEN c.religion_c = 'E' THEN 'Episopalian'
          WHEN c.religion_c = 'EC' THEN 'Ecumencial'
          WHEN c.religion_c = 'H' THEN 'Hindu'
          WHEN c.religion_c = 'HG' THEN 'Huguenot'
          WHEN c.religion_c = 'J' THEN 'Jewish'
          WHEN c.religion_c = 'JW' THEN 'Jehovahs Witness'
          WHEN c.religion_c = 'L' THEN 'Lutheran'
          WHEN c.religion_c = 'MU' THEN 'Muslim'
          WHEN c.religion_c = 'ME' THEN 'Methodist'
          WHEN c.religion_c = 'MEN' THEN 'Mennonite'
          WHEN c.religion_c = 'MO' THEN 'Mormon'
          WHEN c.religion_c = 'M' THEN 'Moslem'
          WHEN c.religion_c = 'N' THEN 'None'
          WHEN c.religion_c = 'NO' THEN 'Nondenominational'
          WHEN c.religion_c = 'O' THEN 'Other'
          WHEN c.religion_c = 'P' THEN 'Protestant'
          WHEN c.religion_c = 'PC' THEN 'Pentecostal'
          WHEN c.religion_c = 'PS' THEN 'Presbyterian'
          WHEN c.religion_c = 'Q' THEN 'Quaker'
          WHEN c.religion_c = 'UN' THEN 'Unknown'
          WHEN c.religion_c = 'UT' THEN 'Unitarian'
          ELSE 'Unknown'    
          END) AS religion,
       UPPER(CONVERT(varchar(20),
          CASE -- Cased out religion codes KMH 06/17/10
          WHEN c.religion_c = 'A' THEN 'A'
          WHEN c.religion_c = 'AG' THEN 'AG'
          WHEN c.religion_c = 'AT' THEN 'AT'
          WHEN c.religion_c = 'B' THEN ' B'
          WHEN c.religion_c = 'BA' THEN 'BA'
          WHEN c.religion_c = 'C' THEN 'C'
          WHEN c.religion_c = 'E' THEN 'E'
          WHEN c.religion_c = 'EC' THEN 'E'
          WHEN c.religion_c = 'H' THEN 'H'
          WHEN c.religion_c = 'HG' THEN 'HG'
          WHEN c.religion_c = 'J' THEN 'J'
          WHEN c.religion_c = 'JW' THEN 'JW'
          WHEN c.religion_c = 'L' THEN 'L'
          WHEN c.religion_c = 'MU' THEN 'MU'
          WHEN c.religion_c = 'ME' THEN 'ME'
          WHEN c.religion_c = 'MEN' THEN 'MEN'
          WHEN c.religion_c = 'MO' THEN 'MO'
          WHEN c.religion_c = 'M' THEN 'M'
          WHEN c.religion_c = 'N' THEN 'N'
          WHEN c.religion_c = 'NO' THEN 'NO'
          WHEN c.religion_c = 'O' THEN 'O'
          WHEN c.religion_c = 'P' THEN 'P'
          WHEN c.religion_c = 'PC' THEN 'PC'
          WHEN c.religion_c = 'PS' THEN 'PS'
          WHEN c.religion_c = 'Q' THEN 'Q'
          WHEN c.religion_c = 'UN' THEN 'UN'
          WHEN c.religion_c = 'UT' THEN 'UT'
          ELSE 'UN'    
          END)) AS religion_code,
         CONVERT(nvarchar(50), --Added ethnicity case statement KMH 5/26/10
         CASE
         WHEN c.race_c = 'H' THEN 'Hispanic'
         ELSE 'Non Hispanic'
         END) AS ethnicity,
       CONVERT(varchar(20), --Added ethnicity case statement KMH 5/26/10
         CASE
         WHEN c.race_c = 'H' THEN '01'
         ELSE '02'
         END) AS ethnicity_code,
       CONVERT(nvarchar(50),
          CASE
          WHEN c.race_c = 'A' THEN 'Asian'
          WHEN c.race_c = 'AI' THEN 'American Indian'
          WHEN c.race_c = 'B' THEN 'African American'
          WHEN c.race_c = 'BR' THEN 'Bi-racial'
          WHEN c.race_c = 'C' THEN 'Caucasian'
          WHEN c.race_c = 'H' THEN 'Hispanic'
          WHEN c.race_c = 'ME' THEN 'Middle Eastern'
          WHEN c.race_c = 'N' THEN 'Native Hawaiian/Other Pacific Islander'
          WHEN c.race_c = 'O' THEN 'Other'
          ELSE 'Other'
          END) AS race_1,
        
       UPPER(CONVERT(varchar(20), 
          CASE 
          WHEN c.race_c is NULL THEN 'U'
          WHEN c.race_c = 'A' THEN 'A'
          WHEN c.race_c = 'AI' THEN 'AI'
          WHEN c.race_c = 'B' THEN 'B'
          WHEN c.race_c = 'BR' THEN 'BR'
          WHEN c.race_c = 'C' THEN 'C'
          WHEN c.race_c = 'H' THEN 'H'
          WHEN c.race_c = 'ME' THEN 'ME'
          WHEN c.race_c = 'N' THEN 'N'
          WHEN c.race_c = 'O' THEN 'O'
          ELSE 'U'
          END))AS race_1_code,
       'ar.client' AS original_table_name
       
       from
       ar.client c 
       INNER JOIN cd.enrollments en ON (c.uniqueid_c = en.clientid_c)
       INNER JOIN cd.episode ep ON (ep.uniqueid_c = en.episodeid_c and ep.clientid_c = c.uniqueid_c)
       inner JOIN dbo.rd_filtered_client_addresses ad ON (ad.clientcode_c = c.clientcode_c)
    where
       (ep.enddate_d is NULL OR ep.enddate_d >= getdate()-729) and
       en.location_c in (select code from dbo.yv_LKUP_OfficeLocation where state in ('GA', 'AL'))
    

    And here is the code for these tables:

    CREATE TABLE dbo.rd_information3_echo (
        [client_id] nvarchar(20),
    [client_information_id] uniqueidentifier,
        [original_table_name] varchar(300),
        [last_name] nvarchar(50),
        [first_name] nvarchar(50),
        [middle_name] nvarchar(15),
        [agency_id_no] nvarchar(20),
        [gender] nvarchar(50),
        [gender_code] varchar(20),
        [ss_number] nvarchar(9),
        [date_of_birth] datetime,
        [street_address_1] nvarchar(50),
        [street_address_2] nvarchar(50),
        [City] nvarchar(50),
        [state] nvarchar(50),
        [state_code] nvarchar(2),
        [zip_code] nvarchar(9),
        [religion] nvarchar(50),
        [religion_code] varchar(20),
        [ethnicity] nvarchar(50),
        [ethnicity_code] varchar(20),
        [race_1] nvarchar(50),
        [race_1_code] varchar(20)
    )
    
    CREATE TABLE dbo.rd_information3_cleaned (
        [client_id] nvarchar(20),
    [client_information_id] uniqueidentifier,
        [original_table_name] varchar(300),
        [last_name] nvarchar(50),
        [first_name] nvarchar(50),
        [middle_name] nvarchar(15),
        [agency_id_no] nvarchar(20),
        [gender] nvarchar(50),
        [gender_code] varchar(20),
        [ss_number] nvarchar(9),
        [date_of_birth] datetime,
        [street_address_1] nvarchar(50),
        [street_address_2] nvarchar(50),
        [City] nvarchar(50),
        [state] nvarchar(50),
        [state_code] nvarchar(2),
        [zip_code] nvarchar(9),
        [religion] nvarchar(50),
        [religion_code] varchar(20),
        [ethnicity] nvarchar(50),
        [ethnicity_code] varchar(20),
        [race_1] nvarchar(50),
        [race_1_code] varchar(20)
    )


     


    Ryan D
    • Editado ironryan77 martes, 24 de enero de 2012 1:30
    martes, 24 de enero de 2012 1:27

Respuestas

  • This is a perfect example of why you should NEVER use SELECT * and should always name the columns in SELECT and INSERT statements!

    The [date_of_birth] column of table dbo.rd_information3_cleaned is the 11th column defined in the table however your CTE returns

    street_address_1 as the 11th column (instead of date_of_birth) therefore you are trying to insert street_address_1 into the [date_of_birth] column.

    Change you code to use column names in both your SELECT and INSERT statements as this will assist you with tracking down simple errors such as this.


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    martes, 24 de enero de 2012 5:05

Todas las respuestas

  • seems like the error occurs with the delete rather than the insert...

    does this table link to any others? Any triggers involved?


    Rgds Geoff
    martes, 24 de enero de 2012 2:05
  • What makes u think the error occurs with the delete?  No, this table does not link to others other than described above.  But there are many triggers on these source tables.
    Ryan D
    martes, 24 de enero de 2012 2:57
  • From your error message:

    SQL Task: Executing the query "DELETE   FROM rd_information3_echo
    where client_id..." failed with the following error: "Conversion failed when converting date and/or time from character string.".

    although looking at it again, it could just be picking up from the 1st line of the query

    Can you seperate your queries into 3 execute SQL elements so that we can prove which line is failing?

    If it isn't the delete then the most likely culprit is :

    ep.enddate_d >= getdate()-729

    possibly some issue wth the values in ep.enddate_d...


    Rgds Geoff
    martes, 24 de enero de 2012 3:03
  • This is a perfect example of why you should NEVER use SELECT * and should always name the columns in SELECT and INSERT statements!

    The [date_of_birth] column of table dbo.rd_information3_cleaned is the 11th column defined in the table however your CTE returns

    street_address_1 as the 11th column (instead of date_of_birth) therefore you are trying to insert street_address_1 into the [date_of_birth] column.

    Change you code to use column names in both your SELECT and INSERT statements as this will assist you with tracking down simple errors such as this.


    Jeff Wharton
    MSysDev (C.Sturt), MDbDsgnMgt (C.Sturt), MCT, MCPD, MCITP, MCDBA
    Blog: Mr. Wharty's Ramblings
    MC ID: Microsoft Transcript

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    martes, 24 de enero de 2012 5:05