Problem with subtraction - hour - hour

2877737

I load data from flat file (txt) to data base using Oracle Data Integrator, są I can modify the interface in ODI to convert them. How ?

to_date(start_zad, '24HH:MI') ?

to_date(stop_zad, '24HH:MI') ?

Paul.

Cookiemonster76

What do you want the date part of those dates to be?

That to_Date will give the 1st day of the current month, is that what you want?

2877737

Of course NOT. I need time of start and end tasks.

From flat file I get it varchar2 (eg 21:22)

Cookiemonster76

You seem to be missing the point.

Dates contain dates. Always.

If you only specify the time and not the date in to_date then oracle will default the date to the first of the current month.

You have to have to have some date, if it's not that then it has to be something else.

And you need to tell oracle what that other date is.

If you don't know how to work out the date you want in code then you need to tell us what date you want so we can help.

2877737

Hmmmmm,

I have in my table two columns type varchar2 where I insert time of start the tasks and time of end tasks.

When I was creating this table I tried to make this columns as type of date or timestamp and convert varchar2 to these formats during inserting to table.

But I had problems so I gave up it.

JonWat

Text in a flat file is not really varchar2, it is just text. Part of the question is: is the date in that flat file? And the second part would be, in what way is it in that file. If your flat file has an entry like:

other columns,2018-02-18 22:10, other columns  (assuming columns delimited in some way, rather than fixed column format)

then you should be able to just specify a format to read the entire column into a date in your table. If your flat file has:

other columns,2018-02-18, 22:10, other columns

i.e. it has the date in one column, and the time in another, then it is probably a little more complicated to get the date into one column in your table. I have no idea how ODI works. In SQL LDR you would do something like the following:

LOAD DATA

INFILE *

INSERT

INTO TABLE TESTDATE

fields terminated by ','

trailing nullcols

(

INPUTCOL1 BOUNDFILLER

,INPUTCOL2 BOUNDFILLER

,mystring

,mydate date  'yyyy-mm-dd hh24:mi' ":INPUTCOL1||:INPUTCOL2"

)

BEGINDATA

2018-02-18,10:15,"Hello"

2018-03-18,22:15,"Goodbye"

If the date is not in the flat file, then where does it come from? Are you always loading today's data? Do you have to supply it manually by extracting it from the file name?

2877737

Hi @JonWat,

it's a simple record from a flat file :

17334;UGGG115W;2017-11-30;J57738;23:55;00:00;4.33

I marked start time (23:55) and end time (00:00)

EdStevens

2877737 wrote:Hi JonWat,it's a simple record from a flat file :17334;UGGG115W;2017-11-30;J57738;23:55;00:00;4.33I marked start time (23:55) and end time (00:00)

So what does start time and end_time actually represent?  Is it the actual time something started/ended?  Or is it when something is scheduled to stop/end regardless of date?

If the former, then it actually started/stopped at the specified time on a particular date - even if you didn't actually capture the date.  And if that is the case, why didn't you capture the date as well?  Or is that what is in the third field?  the '2017-11-30' in your example.  If so, then you need to concatenate that to your time part when you feed it to to_date.  So what is that apparent date, and what are you doing with it now?

Cookiemonster76

Well there is a date in that data - 2017-11-30 - do the start and end times correspond to that date?

2877737

no, the date when a task was started is 17334 (YYDDD) at 23:55. But this task was ended at 00:00 on 17335