Post reply

datetime - with 01/01/1000 date

  • March 1, 2019 at 9:48 am

    #373997

    Am I on the right track here when a vendor sends me data that includes a "begin date" of 01/01/1000 that SSIS will faile to import that date because it falls outside of the accepted date range for the datetime function of " January 1, 1753, through December 31, 9999" according to Microsoft?

    I opened the text file in Notepad++ and found three instances of 01/01/1000 and switched them all to 01/01/1753 and the import worked.

    Next step is to ask the vendor to kindly use 01/01/1753 instead when they don't know what a start date is or just leave it blank? :laugh:

    -Mark
    MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

  • March 1, 2019 at 9:52 am

    #2023306

    Can you use datetime2 in SSIS? This will store 01/01/1000 as a date:
    declare @Date as datetime2(3)
    set @Date ='1000-01-01'
    select @Date

  • March 1, 2019 at 10:32 am

    #2023315

    Or have SSIS convert that value to a different date.  Typically 1900-01-01 is used (date 0 in SQL) for that, unless you have actual data that goes back that far, or close to that far.

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • March 1, 2019 at 10:55 am

    #2023319

    I'm wondering if that value should be a NULL. Otherwise, it wouldn't make sense to have information from the Middle Age or Mayan civilization.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • March 1, 2019 at 11:18 am

    #2023324

    Well, I'm not quite there yet with SSIS to make changes to data as it imports. I'm at the point where I have the "basic" SSIS packages saved from the "Import Data" windows as shown in Section 1 the Stairways to SSIS.

    I agree with using 01/01/1900 makes more sense in this particular case with the data I'm importing on a monthly basis.

    -Mark
    MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

  • March 6, 2019 at 8:46 am

    #2023677

    Use a staging table with the date column defined as varchar to load to initially. Then use a select with a case that changes the date to something in the valid range to insert to the target table.

  • March 6, 2019 at 12:23 pm

    #2023707

    Joe Torre - Wednesday, March 6, 2019 8:46 AM

    Use a staging table with the date column defined as varchar to load to initially. Then use a select with a case that changes the date to something in the valid range to insert to the target table.

    Joe, that's a good tip. I am finding that using a staging table for some imports from outside sources is the best/easiest method for me at the moment with my skillset.

    -Mark
    MSSQL 2019 Standard, Azure Hosted. Techie/Sysadmin by trade; Three years as a "DBA" now.

Viewing 7 posts - 1 through 6 (of 6 total)

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