none
Unicode To DATE IN SSIS

    Question

  • Hello all,

    I have a conversion problem here.. hope you guys can help...

    I have a date column in unicode format... I have used derived column and changed it to (DT_DBDATE)(column1) and try to load in destination table... but i am getting following error..

    [Derived Column [296]] Error: An error occurred while attempting to perform a type cast.

    Following is the sample data what i have..

    0000-00-00

    2013-06-21

    2013-06-22

    2013-07-01

    0000-00-00

    I have the expression like this but then also it fails..

    (DT_DBDATE)((DT_STR,4,1252)SUBSTRING([Copy of columnA],1,4) + "-" + (DT_STR,2,1252)SUBSTRING([Copy of columnA],6,2) + "-" + (DT_STR,2,1252)SUBSTRING([Copy of columnA],9,2))

    This i show the data looks... Please help 


    Thanks, Anji

    Tuesday, July 02, 2013 9:08 PM

Answers

  • 0000-00-00 is not a valid date.  valid date ranges for SQL Server are:

       0001-01-01 through 9999-12-31

    msdn.microsoft.com/en-us/library/bb630352.aspx


    To clarify in your replace, instead of replacing 0000-00-00 with " 0", change it to "0001-01-01"  if you are using datetime then use the min range of datetime which is 1753-01-01
    • Edited by Jason-420805 Tuesday, July 02, 2013 9:51 PM clarify
    • Proposed as answer by Koen Verbeeck Wednesday, July 03, 2013 12:18 PM
    • Marked as answer by Anji1407 Wednesday, July 03, 2013 8:54 PM
    • Unmarked as answer by Anji1407 Wednesday, July 03, 2013 8:54 PM
    • Marked as answer by Anji1407 Wednesday, July 03, 2013 8:55 PM
    Tuesday, July 02, 2013 9:36 PM

All replies

  • I used the latest expression but this one too fails...

    (DT_DBDATE)REPLACE([Column1],"0000-00-00"," 0")


    Thanks, Anji

    Tuesday, July 02, 2013 9:26 PM
  • 0000-00-00 is not a valid date.  valid date ranges for SQL Server are:

       0001-01-01 through 9999-12-31

    msdn.microsoft.com/en-us/library/bb630352.aspx


    To clarify in your replace, instead of replacing 0000-00-00 with " 0", change it to "0001-01-01"  if you are using datetime then use the min range of datetime which is 1753-01-01
    • Edited by Jason-420805 Tuesday, July 02, 2013 9:51 PM clarify
    • Proposed as answer by Koen Verbeeck Wednesday, July 03, 2013 12:18 PM
    • Marked as answer by Anji1407 Wednesday, July 03, 2013 8:54 PM
    • Unmarked as answer by Anji1407 Wednesday, July 03, 2013 8:54 PM
    • Marked as answer by Anji1407 Wednesday, July 03, 2013 8:55 PM
    Tuesday, July 02, 2013 9:36 PM