Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Friday June 09, 2006
02:10 PM

Oracle gem of the day: variable date range in WHERE clause

[ #29862 ]

This was developed in response to a request for help from a former boss. The problem is: I want to select all records from a table more recent than a certain date. In most cases, I want all records from the most recent 24 hours. However, on Mondays, I want all records from the most recent 72 hours. I know this means I need a conditional in the WHERE clause somewhere, but what is it, and where does it go?

Answer (and this was fun to figure out together):

SELECT *
FROM datetable
WHERE thedate < CASE WHEN TO_CHAR(SYSDATE, 'DY') = 'MON' THEN SYSDATE - 3
                     ELSE SYSDATE - 1 END
/

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.