DateTime issue in DataServices

[Former Member]

Good Morning All,

I'm having a weird issue with an ETL in DS. What is going on is we have 2 data flows that import information, one imports sale header info and the 2nd imports sales detail information. For one of the fields that is being imported is for our SaleDate. For our DF that imports the Sales header information the SalesDate mapping looks like this:

IFTHENELSE(BISALESORDERMAST.SALESDATE > '1900.01.01 00:00:00',to_date(to_char(BISALESORDERMAST.SALESDATE, 'yyyy.mm.dd hh24:mi:ss'),'yyyy.mm.dd') , '1900.01.01 00:00:00')

So when the column is populated we get dates like so: '2014-01-05 00:00:00.000'

Now in our DF that imports the sales detail information, for our SalesDate column we're using this statement:

IFTHENELSE(BISALESORDERMAST.SALESDATE > '1900.01.01 00:00:00', to_date(to_char(BISALESORDERMAST.SALESDATE, 'yyyy.mm.dd hh24:mi:ss'),'yyyy.mm.dd'), to_Date(to_char(BISALESORDERMAST.CREATEDATE, 'yyyy.mm.dd hh24:mi:ss'),'yyyy.mm.dd'))

So when the column populates here we get dates like this: '2014-01-05 02:03:00.000'

The SalesDate for the sales detail table used to look identical as the sales header salesdate column until a single join was removed between the 2 files that we use to populate the sales detail table. Has anyone else experienced this or know why this would happen? Also how do I make the time all zeroes but keep the format of '1900.01.01 00:00:00'? Why would removing the join now populate the time, where before removing the join the time was set to 00:00:00.000'?

Any help is appreciated!!! Thanks!

[former_member187605 · Accepted solution]

Your syntax is not 100% correct:

Suppose BISALESORDERMAST.SALESDATE contains 2014-01-05 02:03:00.000,

then to_char(BISALESORDERMAST.SALESDATE, 'yyyy.mm.dd hh24:mi:ss') results into the character string '2014-01-05 02:03:00.000' and your statement

to_date(to_char(BISALESORDERMAST.SALESDATE, 'yyyy.mm.dd hh24:mi:ss'),'yyyy.mm.dd') is then equivalent to to_date( '2014-01-05 02:03:00.000','yyyy.mm.dd').

The format does not match the input string! Hence the unpredicatble result?!

Rewriting as to_date(to_char(BISALESORDERMAST.SALESDATE, 'yyyy.mm.dd'),'yyyy.mm.dd') will get you rid of the time part automatically .

[Former Member · Accepted solution]

Dirk,

I apologize, I'm still a little confused. The statement in the data flow has always been:

IFTHENELSE(BISALESORDERMAST.SALESDATE > '1900.01.01 00:00:00', to_date(to_char(BISALESORDERMAST.SALESDATE, 'yyyy.mm.dd hh24:mi:ss'),'yyyy.mm.dd'),

to_Date(to_char(BISALESORDERMAST.CREATEDATE, 'yyyy.mm.dd hh24:mi:ss'),'yyyy.mm.dd'))

before removing the join produced '2014-01-05 00:00:00.000' the table this comes from either BISALEORDERMAST.SALESDATE or BISALEORDERMAST.CREATEDATE do have the times in there. So I guess Im confused what removing the join did to make the time populate. So with that statement produced the following:

With the join:

2014-01-05 00:00:00.000

after removing the join:

2014-01-05 02:03:00.000

If the values from the salesordermast table remained the same..why would removing the join allow the times to come through when the syntax was the same?

Thank you,

Tiff

[former_member187605 · Accepted solution]

There is no obvious reason why. In fact, DS should issue an error message. But it doesn't. An until now undetected feature of the software .

As a side-effect, the outcome of your statement is unpredicable. That's what you are experiencing: you change the code, the result may be different.

Correct your syntax and you'll always get the desired results.

[Former Member · Accepted solution]

Gotcha. I have corrected the code and its definitely coming through correctly without defaulting the values. Thank you again for your help Dirk!!!!!!

Much Appreciated,

Tiff