CODEWITHSUNDEEP
sqloracle-databasetimewhere-clause
Rob Morris
Rob Morris

Reputation: 137

Oracle SQL - Shows all jobs logged after 5pm

Im trying to produce a report that shows all jobs that were logged after 5pm The code I've got so far is

  select
    job.job_number,
    job.job_entry_date,
    job.site_code,
    JOB.JOB_ENTRY_DATE as Job_logged,
    job.target_comp_date as Target_On_Site,
    job.actual_START_date as Actual_On_Site,
    JOB.actual_COMP_DATE as Job_Completed_Time,

from
    job

where
    job_status_log.allocated_officer = 'IDVE' and
job_status_log.status_code in ('5100','5200','5300','5400') 

order by
job.job_number

I've tried adding the following code

job.job_entry_date >= TO_DATE ('5:00:00 PM', 'HH:MI:SS PM')

to the WHERE section but this hasn't brought back the right records - it only works correctly for jobs logged on 01/05/2019. All jobs from that point on are just listed even if logged before 5pm.

Any help on where I'm going wrong would be welcome.

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270513

One method is:

WHERE TO_CHAR(job.job_entry_date, 'HH24:MI:SS') >= '17:00:00'

Upvotes: 1

Related Questions