Re: Date math help with 8.0.5

Duh, never mind, found my own problem - switched my parameters in the mod() function

GIGO Steven Cardinal wrote in message <88epv3$k51_at_journal.concentric.net>...
>Hello,
>
>I'm trying to run a query (actually a view once I get the query to work)
>that will pull data based on some date parameters, ie :
>
>Show me all users whose STARTTIME < SYSDATE and ENDTIME > SYSDATE
> AND this hour needs to be based on an assigned frequency. Say they are a
>valid record every 2 hours between the starttime and endtime. I've tried
>the following query, but it doesn't always work:
>
>
>select R.USERID, U.EMAIL, U.FNAME, D.DRUGNAME, D.MESSAGE
>from EPROMPT_USER U, EPROMPT_DRUG D, EPROMPT_RX R
>where U.USERID = R.USERID
>and D.DRUGID = R.DRUGID
>and R.STARTTIME < (SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL)
>and R.ENDTIME > (SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL)
>and 0 = (SELECT MOD((TO_CHAR(SYSDATE, 'HH24'))-R.FREQUENCY, R.STARTTIME)
>FROM DUAL)
>
>
>My starttime, endtime and frequency are Numbers (0-23, 0-23 and 1-8
>respectively) and I'm formatting SYSDATE as HH24. I get data if SYSTIME is
>12:xx noon-ish. It's now 1:00 and I get no records, based on my data I
>should get 2 records.
>
>If I reverse the last AND to
>and = (SELECT MOD((TO_CHAR(SYSDATE, 'HH24'))-R.FREQUENCY, R.STARTTIME) FROM
>DUAL) = 0
>
>I get ORA-00936: missing expression
>
>Any ideas? I'm new to Oracle and can't find much info in any of my Oracle
>books (SQL*Plus, DBA handbook, Complete Reference or DBA guide)
>
>Thanks
>Steve
>
>
>
Received on Wed Feb 16 2000 - 00:00:00 CST