locked
Default Date Value Error? RRS feed

  • Question

  • Good morning all,

    I'm not sure if this is the right forum, but I'll take a stab here and, if I'm in the wrong place, please let me know.

    We recently upgraded from Office 2013 to Office 2016. Since that upgrade, SSIS has been hiccupping on some date fields in Excel source files. It seemed that one instance of the issue was solved by going into the Excel source and using Text to Columns on the errant date column. I even tried reprocessing an Excel file from last month that ran fine - I had yet to upgrade to O2016 - and now it has the same problem on a date field.

    However, now I've got a column on which the Text to Columns solution is not working. So, in trying to figure this out, I thought I'd deconstruct the date into text and then see how that would import.

    I used CONCAT(MONTH(V2),"/",DAY(V2),"/",YEAR(V2)) to get the date. However, as I was contemplating what else I might do, if this idea didn't pan out, I noticed that the date for a blank cell was "1/0/1900."

    This didn't look right. Why is the day a "0?" To the best of my recollection, had I done this same thing on Excel 2013, it would have returned "1/1/1900." In fact, in the dialog to set up the function, it lists the limits of the DAY function as 1 to 31, but, if performed on a blank cell, it returns "0."

    We've all upgraded to O2016 - I was last to ensure we could recover if there was a disaster from upgrading - so I can't test what this code would return on an earlier version - I can do that at home, but that won't be until later in the day. I suspect that this is what's causing my problem in SSIS, but I don't know. Might it be that the way a blank cell is converted to a date in Excel have relevance to how the Excel source tool will work in SSIS? Am I guessing right that this is what's causing SSIS to get a truncation error?

    Monday, August 6, 2018 2:56 PM

Answers

  • This has not changed. In all versions of Excel for Windows, 0 (or a blank) is displayed as the 0th of January, 1900 (*)

    This is weird, but it is by design - if 1 corresponds to the 1st of January, 1900, then 0 should correspond to the 31st of December, 1899. But Excel (and its predecessor Lotus 1-2-3) were designed to handle only years from 1900 on. To make an exception would have caused the developers nightmares. To change it now would break existing workarounds.

    (*) Unless Excel has been set to use the 1904 date system (for Mac compatibility).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Adam Quark Wednesday, June 26, 2019 3:38 PM
    Monday, August 6, 2018 8:52 PM

All replies

  • This has not changed. In all versions of Excel for Windows, 0 (or a blank) is displayed as the 0th of January, 1900 (*)

    This is weird, but it is by design - if 1 corresponds to the 1st of January, 1900, then 0 should correspond to the 31st of December, 1899. But Excel (and its predecessor Lotus 1-2-3) were designed to handle only years from 1900 on. To make an exception would have caused the developers nightmares. To change it now would break existing workarounds.

    (*) Unless Excel has been set to use the 1904 date system (for Mac compatibility).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by Adam Quark Wednesday, June 26, 2019 3:38 PM
    Monday, August 6, 2018 8:52 PM
  • This has not changed. In all versions of Excel for Windows, 0 (or a blank) is displayed as the 0th of January, 1900 (*)

    This is weird, but it is by design - if 1 corresponds to the 1st of January, 1900, then 0 should correspond to the 31st of December, 1899. But Excel (and its predecessor Lotus 1-2-3) were designed to handle only years from 1900 on. To make an exception would have caused the developers nightmares. To change it now would break existing workarounds.

    (*) Unless Excel has been set to use the 1904 date system (for Mac compatibility).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Interesting. Everyone here at work also thought the default was "1." I'd've sworn on a stack of bibles that, when the date is null or blank, that it went to "1/1/1900." I could not ever recall seeing "1/0/1900."
    Monday, August 6, 2018 8:55 PM