none
SSIS 2005 two digit year

    Question

  • Hello everyone,

    I am encountering a problem with a SSIS 2005 package. In the package I have a source table and a destination table. In the source table there is a field called validity_date which is of type varchar. I perform a Data Conversion using DT_Date to convert this field to datetime. I insert the converted field in the destination table.

    In the source table the field is stored in the format DD/MM/YY and the two digit cut off year for SQL server 2005 is 2050. The problem that I am encountering is that the date 23/12/50 in the source table gets inserted as '1950-12-23 00:00:00.000' instead of '2050-12-23 00:00:00.000' in the destination table. Since the two digit cut off year is 2050, the year 50 should be interpreted as 2050 instead of 1950.

    Can anyone please provide me some feedback regarding this issue?

    Thanks and kind regards.

     

    Tuesday, August 16, 2011 7:23 AM

Answers

  • you can use a derived column with this expression : (SUBSTRING([dateColumn],7,2)>"50" ? "19" : "20") + SUBSTRING([dateColumn],7,2) +"-"+ SUBSTRING([dateColumn],4,2)+"-"+ SUBSTRING([dateColumn],1,2)
    http://www.rad.pasfu.com
    • Proposed as answer by Christa Kurschat Tuesday, August 16, 2011 9:14 AM
    • Marked as answer by Eileen Zhao Monday, August 22, 2011 10:01 AM
    Tuesday, August 16, 2011 7:36 AM
    Moderator

All replies

  • you can use a derived column with this expression : (SUBSTRING([dateColumn],7,2)>"50" ? "19" : "20") + SUBSTRING([dateColumn],7,2) +"-"+ SUBSTRING([dateColumn],4,2)+"-"+ SUBSTRING([dateColumn],1,2)
    http://www.rad.pasfu.com
    • Proposed as answer by Christa Kurschat Tuesday, August 16, 2011 9:14 AM
    • Marked as answer by Eileen Zhao Monday, August 22, 2011 10:01 AM
    Tuesday, August 16, 2011 7:36 AM
    Moderator
  • I aggree with Reza.

    Instead of focusing on "two digit year cutoff" should leave you better make sure that the century is set correctly.

    Tuesday, August 16, 2011 9:20 AM