# 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".
###### Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x

Commented:
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
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

Commented:
Try now() - 31 instead of date() - 31 and see if it works.
0

Commented:
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

Commented:
Forced accept.

Computer101