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

[Former Member]

Hi Tiffany,

This is interesting! Are the data type of both BISALESORDERMAST.SALESDATE and BISALESORDERMAST.CREATEDATE same?

Has anyone else experienced this or know why this would happen?

I haven't come across this situation.

Also how do I make the time all zeroes but keep the format of '1900.01.01 00:00:00'?

The syntax looks right, but may be you could try over-riding the time to 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'?

What kind of join are you trying to remove? Try executing the DF in debug mode and check the results at each stage. That could help you identify the place (or query) where this is happening.

Arun J

[Former Member]

Arun,

1. The datatypes of both the BISALESORDERMAST.SALESDATE and BISALESORDERMAST.CREATEDATE are the same.

2. I did over ride the time with 00:00:00 and this does work.

3. The join that was removed was an outter join since the salesdate was not ALWAYS present so we then would use the createdate, so now currently the 2 files in the data flow are an inner join now.

4. I will definitely be running this in debug mode at some point today to see if I can find where it happening! Just still little confused as to why a join would adjust the format!

Thank you!