This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,912 Users
  • 2,269,775 Discussions
  • 7,916,823 Comments

Discussions

Problem with subtraction - hour - hour

2

Answers

  • 2877737
    2877737 Member Posts: 45
    edited Mar 14, 2018 8:32AM

    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
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Mar 14, 2018 8:40AM

    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
    2877737 Member Posts: 45
    edited Mar 14, 2018 9:05AM

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

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

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Mar 14, 2018 9:24AM

    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
  • 2877737
    2877737 Member Posts: 45
    edited Mar 14, 2018 9:41AM

    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
    JonWat Member Posts: 563 Silver Badge
    edited Mar 14, 2018 10:00AM

    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
    2877737 Member Posts: 45
    edited Mar 14, 2018 10:15AM

    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
    EdStevens Member Posts: 28,778 Gold Crown
    edited Mar 14, 2018 10:30AM
    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
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Mar 14, 2018 10:32AM

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

  • 2877737
    2877737 Member Posts: 45
    edited Mar 14, 2018 10:50AM

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

This discussion has been closed.