Datetime to Date conversion

[Former Member]

Hi,

We use Data Integrator 11.7.3.4, the source and target databases are Oracle 10g.

We need to convert a datetime column ('01/01/2009 17:13:00') to a simple date ('01/01/2009 00:00:00').

In a query transform, I tried changing the right column type from 'datetime' to 'date' , but with no success.

Is there a clean way to remove the time from a datetime (using to_char then to_date isn't one) ? Setting the time to '00:00:00' would achieve the same goal by the way.

Thanks !

[werner_daehn · Accepted solution]

Yes, I would to_date(to_char()) it

to_date(to_char(sysdate(), 'YYYY.MM.DD'), 'YYYY.MM.DD')

A trunc(date, 'DAY') function would be nice, wouldn't it?

[Former Member · Accepted solution]

Yes, a TRUNC(date) would be great.

I'll use TO_DATE(TO_CHAR(xxx)) then, but it really hurts my eyes...

Shouldn't having the right column in a query transform set to 'date' (instead of datetime type) remove the time ? Is it a bug in DI 11.7.3.4 ?