Home » SQL & PL/SQL » SQL & PL/SQL » select START_TIME = TO_DATE returns 0 (RDBMS 12.2..0.1 on linux)
select START_TIME = TO_DATE returns 0 [message #676306] Wed, 29 May 2019 13:57 Go to next message
evoradba
Messages: 112
Registered: April 2005
Location: Canada
Senior Member
Hello
I have this sql statment
SELECT JOB_ID as jobId,JOB_NAME as jobName,JOB_DESC as jobDesc,ALLOW_OPS_LAUNCH as allowOpsLaunch,JOB_ARGS as jobArgs,SUBMIT_TIME as submitTime,START_TIME as startTime,END_TIME as endTime,JOB_STATUS as jobStatus,TOTAL_RECORDS as totalRecords,CURRENT_RECORD as currentRecord,FAILED_RECORDS as failedRecords,SKIPPED_RECORDS as skippedRecords,SUCCESSFUL_RECORDS as successfulRecords,MESSAGE as message,ERROR_CODE as errorCode,REPORT_NAME as reportName,PRODUCT_CODE as productCode,USERID as userid FROM TABLE o WHERE o.PRODUCT_CODE IN ('AAI', 'FF', 'LL', 'AA', 'NN') AND o.START_TIME = TO_DATE('2019-05-14', 'YYYY-MM-DD') ORDER BY o.SUBMIT_TIME desc, o.JOB_ID desc

START_TIME = TO_DATE = does not return any data,

I figure it out thanks

[Updated on: Wed, 29 May 2019 14:32]

Report message to a moderator

Re: select START_TIME = TO_DATE returns 0 [message #676307 is a reply to message #676306] Wed, 29 May 2019 14:47 Go to previous messageGo to next message
Michel Cadot
Messages: 66687
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As already told and repeated to you:

Michel Cadot wrote on Mon, 27 October 2014 21:49

...
Please How to use [code] tags and make your code easier to read.
Michel Cadot wrote on Mon, 27 October 2014 21:56

...
Please read and APPLY OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
BlackSwan wrote on Tue, 12 February 2019 16:03
...
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...

And format your query, if you don't know how to do it, learn it using SQL Formatter.


Re: select START_TIME = TO_DATE returns 0 [message #676320 is a reply to message #676306] Thu, 30 May 2019 06:54 Go to previous messageGo to next message
EdStevens
Messages: 1139
Registered: September 2013
Senior Member
I think I have a pretty good idea, but until you format your code, as you've been asked repeatedly in multiple threads, I will say no more.
Re: select START_TIME = TO_DATE returns 0 [message #676321 is a reply to message #676320] Thu, 30 May 2019 07:12 Go to previous messageGo to next message
cookiemonster
Messages: 13720
Registered: September 2008
Location: Rainy Manchester
Senior Member
Looks like the OP has fixed it according to the update.
Re: select START_TIME = TO_DATE returns 0 [message #676322 is a reply to message #676321] Thu, 30 May 2019 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 66687
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and didn't bother to tell us what was the problem and solution.

Re: select START_TIME = TO_DATE returns 0 [message #676346 is a reply to message #676322] Mon, 03 June 2019 11:01 Go to previous message
Bill B
Messages: 1909
Registered: December 2004
Senior Member
I suspect that your start_time column contains time information. Try the following instead. Please note that a table name called "TABLE" is invalid because it is a reserved word. I substituted the name my_table

  SELECT Job_id                 AS Jobid,
         Job_name               AS Jobname,
         Job_desc               AS Jobdesc,
         Allow_ops_launch       AS Allowopslaunch,
         Job_args               AS Jobargs,
         Submit_time            AS Submittime,
         Start_time             AS Starttime,
         End_time               AS Endtime,
         Job_status             AS Jobstatus,
         Total_records          AS Totalrecords,
         Current_record         AS Currentrecord,
         Failed_records         AS Failedrecords,
         Skipped_records        AS Skippedrecords,
         Successful_records     AS Successfulrecords,
         MESSAGE                AS MESSAGE,
         ERROR_CODE             AS Errorcode,
         Report_name            AS Reportname,
         Product_code           AS Productcode,
         Userid                 AS Userid
    FROM My_table O
   WHERE     O.Product_code IN ('AAI',
                                'FF',
                                'LL',
                                'AA',
                                'NN')
         AND O.Start_time >= TO_DATE ('2019-05-14', 'YYYY-MM-DD') 
         AND O.Start_time < TO_DATE ('2019-05-15', 'YYYY-MM-DD')
ORDER BY O.Submit_time DESC, O.Job_id DESC

[Updated on: Mon, 03 June 2019 11:04]

Report message to a moderator

Previous Topic: How to write SQL Query to get desire output
Next Topic: Approximate 50MB files per day to be saved in Oracle Database
Goto Forum:
  


Current Time: Sat Nov 16 18:49:22 CST 2019