CODEWITHSUNDEEP
sqloracle-databaseselect
Jonathan Morningstar
Jonathan Morningstar

Reputation: 431

How to I get a current date but with a fixed time in my SQL Where

In my where I want to get is_my_day < current date with a fixed time (ie 0600 hrs). What's the syntax? This is the current format but I want to specify a fixed time for my automated report. I am using Oracle 10.

WHERE
TO_CHAR(MOPACTIVITY.MOPEND, 'yyyy-mm-dd hh24:mi') < TO_CHAR(TRUNC(CURRENT_DATE - 8/24,'hh24'), 'yyyy-mm-dd hh24:mi')

Upvotes: 1

Views: 1813

Answers (2)

schurik
schurik

Reputation: 7928

There are several ways to do this:

Option one you compare DATE-values direct

WHERE
   MOPACTIVITY.MOPEND < 
                        trunc(SYSDATE, 'DD') + INTERVAL '06:00' HOUR TO MINUTE

SYSDATE - current date , current time

TRUNC(SYSDATE, 'DD') - current date, 00:00 hrs

'TRUNC(SYSDATE, 'DD') + INTERVAL '06:00' HOUR TO MINUTE' - current date, 06:00 hrs

Option two: you convert date values to strings und compare strings

WHERE
 TO_CHAR(MOPACTIVITY.MOPEND, 'yyyymmddhh24mi') < 
                                          to_char(SYSDATE, 'yyyymmdd') || '0600'

to_char(SYSDATE, 'yyyymmdd') -- returns 20130726

Upvotes: 4

Miklos Aubert
Miklos Aubert

Reputation: 4575

This looks a bit ugly IMO but it's the only thing I can think of for now.

WHERE TO_CHAR(MOPACTIVITY.MOPEND, 'yyyy-mm-dd hh24:mi')
    < TO_CHAR(TRUNC(TO_DATE(TRUNC(CURRENT_DATE)) + 6/24, 'hh24'), 'yyyy-mm-dd hh24:mi')

Upvotes: 0

Related Questions