Auto Update turns NULL Datetime into dates?

system · September 27, 2013, 1:33am

I have just noticed the following issue:

When using Auto Update against a table that has a bunch of datetime columns, Data Services turns NULL values into ‘1900-01-01’ dates?

In the source data, these datetime columns are NULL, they have no values of any kind. When I re-create the output table in the Data Flow as a template table, set to drop and re-create, all the datetime columns in question are NULL. (good).

When I then import the template table but still flag to delete the contents on loading, the datetime columns remain NULL. (good again).

When I then disable the deletion flag and set the table to Auto Update (regardless if I allow Merge/Upsert or not), these datetime columns suddenly get the value: 1900-01-01 00:00:00.0000000 (NOT GOOD!)

My situation is as follows:

DS: Data Services 4.1 SP1 Patch 4
OS: MS Windows Server 2008 R2
Target DB: MS SQL Server 2012
Repository DB: MS SQL Server 2008

In the target table, the columns in question were created as DATETIME2 columns.

If I Trace the SQL Load, Data Services is actually sending NULL values to the database.

Is this behaviour a SQL Server 2012 “feature”? Or is this a bug in Data Services 4.1 somehow? I know Sp2 is out there but it will take me a while to upload the installer to that server as it’s geographically separated from me.

Anyone seen this issue before?

ErikR (BOB member since 2007-01-10)

system · September 27, 2013, 12:03pm

Hi,
do you have a default value set for your date column in SQL Server?

cedrickb (BOB member since 2005-08-19)

system · September 30, 2013, 9:35pm

No, no default date is set. And the columns appear to be populated by very random timestamps.

ErikR (BOB member since 2007-01-10)

system · October 1, 2013, 3:36am

take the SQL that you see in the trace log and execute from SQL Server
Query Analyzer, and see if the same issue happens

manoj_d (BOB member since 2009-01-02)

system · October 2, 2013, 2:35am

That is the most strangest thing, according to SQL Trace log, the UPDATE sets the datetime column value to NULL. But when I look in the table, there is some random timestamp in there. But looking at the table/column properties, I can’t see any reason for this behaviour.

As a workaround, we now populate these datetime fields with a default timestamp. Then everything works as it should. However, due to project deadlines I am not in a position to spend lots of time experimenting with this.

ErikR (BOB member since 2007-01-10)

system · October 2, 2013, 1:49pm

The new and mysterious date time stamps are all random, or are some randomly assigned to 1900? If the later, I would check to see if some of the ‘NULL’ are actually 0.

DanHosler (BOB member since 2013-06-19)

system · October 3, 2013, 1:15am

No, they are very random timestamps that seems to belong to other records - I have to free myself up to experiment with this some more.

We have just installed SP2, I must check to see if this behaviour has now changed.

ErikR (BOB member since 2007-01-10)

system · March 17, 2015, 1:29am

In the case above (using auto correct load) where a default value is written to the table datetime column whilst the database is not configured to use a default value and NULL is passed to the datetime column in DS manoj_d’s solution (taken from https://bobj-board.org/t/165700) works:

The above is to be done in the Object Library and is valid for template and imported tables.

beltmanjr (BOB member since 2012-07-29)

system · October 9, 2015, 1:41am

I have ran into something similiar to this and it has nothing to do with the underlying database or tables. The issue I ran into was when reading an Excel spreadsheet with obvious datetime values I was getting the ugly ‘1900-01-01’ values when there were blank cells. I’m reading the date cells as Datetime type and when I dump this straight into a csv file I get the ‘1900-01-01’.
So this has something to do with how BODS reads those blank values. Here’s how I fixed that, I used an ifthenelse function on each date field as such:

ifthenelse(Query.DateField = ‘1900.01.01 00:00:00’, NULL, Query.DateField)

For a NULL value in missing dates

OR

ifthenelse(Query.DateField = ‘1900.01.01 00:00:00’, ’ ', Query.DateField)

For a blank in missing dates (just remember to put in enough spaces to cover the length of the date field, I used 25)

This is set in a query transform right after the spreadsheet input when mapping it out. It’s a manual fix but works. The field coming into the query transform is datetime and the field going out to the SQL Server table is a varchar to get it set to a blank value in the table OR set the value in the table to datetime for a NULL value.

CCRDad (BOB member since 2014-01-27)

system · December 8, 2016, 2:34pm

This is SAP Data Services behavior.
Please vote up an Enhancement to set the SAP NULL TO NULL on the job level on the SAP Idea place
https://ideas.sap.com/D8223

Here is some documentation on SAP Null values:

SAP Data Services Supplement for SAP - DS 4.2 SP8

http://help.sap.com/businessobject/product_guides/sbods42/en/ds_42_sap_en.pdf

9.3.2 Working with null values from SAP applications (P197)

9.3.4 Null dates (this seems to be talking about ADF)
The concept of a null date in SAP applications is represented by a value called INITIAL that is a property of every SAP application

In the default configuration of Data Services (where Convert SAP null to null is disabled), INITIAL dates are written to an ABAP data file as NULLs. In ABAP data files, NULLs are represented by empty strings.

They are then read by the parent data flow, which converts every empty string to a single space. If passed through again without data type or value change, Data Services converts the single blank into 01-JAN-1900 before loading it into a target.
[/url]

JDRoble (BOB member since 2009-06-22)