datetime - with 01/01/1000 date

Question

datetime - with 01/01/1000 date

usererror

Ten Centuries

Points: 1148
More actions
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.

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

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

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

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

ScottPletcher SSC Guru Points: 101098 More actions · Answer 2

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

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

usererror Ten Centuries Points: 1148 More actions · Answer 4

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.

Joe Torre SSChampion Points: 10248 More actions · Answer 5

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.

usererror Ten Centuries Points: 1148 More actions · Answer 6

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.