Querying Date using MS Query Editor for Oracle DB

In Excel's MS Query Editor, I entered this in the criteria
 date()-31
and picked this in the drop down above
is greater than or equal to
but I'm getting a "Syntax error in date".
error_proneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
Try now() - 31 instead of date() - 31 and see if it works.
0
Jinesh KamdarCommented:
Scratch that, even that doesn't work. I tried lots of combinations, but it just doesn't accept any function in the criteria. Work-around : Put some date value in there (say 31-jan-2008) and then when the wizard completes and it opens in the MS Query window, change the criteria to (date() - 31) and then it works.
0
schwertnerCommented:
The Oracle syntax is SYSDATE-31.


Excel dates have nothing to do with Oracle dates:

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.

It is hard to say how to solve your problem,
but somewher you have to do the transformation given below:
According
http://forums.oracle.com/forums/thread.jspa?messageID=2264313
one way to solve the problem is to use functions:

CREATE OR REPLACE FUNCTION GETDATENUMBER(TMPVAR DATE) RETURN NUMBER IS
VAL NUMBER;
BEGIN
SELECT TRUNC(TMPVAR - TO_DATE('01011900')+2) INTO VAL FROM DUAL;
RETURN VAL;
END;
/

if you want convert number to date you can this

CREATE OR REPLACE FUNCTION GETNUMBERDATE(TMPVAR NUMBER) RETURN DATE IS
VAL DATE;
BEGIN
SELECT TO_CHAR(39014 + TO_DATE('01011900')- 2,'dd.mm.yyyy') INTO VAL FROM DUAL;
RETURN VAL;
END;
/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.