Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Problem with subtraction - hour - hour
Answers
-
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.
-
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?
-
Of course NOT. I need time of start and end tasks.
From flat file I get it varchar2 (eg 21:22)
-
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.
-
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.
-
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?
-
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)
-
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?
-
Well there is a date in that data - 2017-11-30 - do the start and end times correspond to that date?
-
no, the date when a task was started is 17334 (YYDDD) at 23:55. But this task was ended at 00:00 on 17335