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?

[Webinar] Streamline your web hosting managementRegister Today

x
 
schwertnerConnect With a Mentor 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
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
 
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
All Courses

From novice to tech pro — start learning today.