View previous topic :: View next topic
|
Author |
Message |
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. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
simple:
Code: |
CREATE_DTE < CURRENT_DATE - 1 days
OR ( create_DTE = CURRENT_DATE - 1 days
AND CREATE_TIME < '22.00.00'
|
more performant:
Code: |
AND CREATE_DTE < CURRENT_DATE
AND NOT ( CREATE_DTE = CURRENT_DATE - 1 days
AND CREATE_TIME >= '22.00.00' ) |
date-functions:
Code: |
and timestamp (create_dte, create_time) < timestamp(current_date - 1 days, '22:00:00') |
|
|
Back to top |
|
|
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; |
|
Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007 Posts: 2455 Location: Hampshire, UK
|
|
|
|
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)" |
|
Back to top |
|
|
Smita.t2
New User
Joined: 17 Apr 2012 Posts: 31 Location: Bangalore
|
|
|
|
You are correct Nic. |
|
Back to top |
|
|
|