Problem with DATE and TIME

mahesh pillai · New User Joined: 04 Oct 2011 Posts: 6 Location: india

Hi,
I have a table from where I need to extract the order details. The requirement is such that the orders details should be older than todays date i.e less than current date and time less than 2200 HRS from the previous day.

But the problem is that whenever I use CREATE_TIME < '22.00.00' it removes all the orders between 22-23.59 for all the dates and not just previous day.

I am using the conditions as below:

AND CREATE_DTE < CURRENT_DATE
AND CREATE_TIME < '22.00.00'

Can anyone help me out in extracting all the details for orders created less than current date and time less than 2200 HRS from the previous day and not every day.

The datatypes are as below:

CREATE_DTE DATE
CREATE_TIME TIME

Thanks.

GuyC · Posted: Wed Jul 01, 2015 1:47 pm

simple:

Smita.t2 · New User Joined: 17 Apr 2012 Posts: 31 Location: Bangalore

please try

select * from table
Where CREATE_DTE <= CURRENT_DATE - 2
or ((CREATE_DTE < CURRENT_DATE-1)
and (CREATE_TIME < '22.00.00'))
with ur;

Nic Clouston · Posted: Wed Jul 01, 2015 4:42 pm

Well, of course it will remove those from all previous dates as you say "less than today's date" but do not say "but greater than 2 days ago". You need "equal to (today's date -1 day)"

Smita.t2 · New User Joined: 17 Apr 2012 Posts: 31 Location: Bangalore

You are correct Nic.