dates [message #39596] |
Thu, 25 July 2002 08:26 |
Leimbach
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
I can't seem to find the right syntax to use to query an Oracle 8 database using auditing software. The owner of the database says contact the software company and vice versa so I haven't gotten help from those two sources. I've had introduction to programming courses, but I don't know enough.
The date field is 8 characters. If I was using Access to do an ODBC query the format would be MM/DD/YYYY. The software program uses a Where format that works fine for queries except for date queries. Example: Where x = y
How do I format a where statement that works?
What format? What quotes, if any?
I get Oracle error message 1843 NOT A VALID MONTH
I get Oracle error message 932 INCONSISTANT DATA TYPES
911 INVALID CHARACTER
Help please.....
Thanks!
|
|
|
Re: dates [message #39600 is a reply to message #39596] |
Thu, 25 July 2002 09:10 |
Todd Barry
Messages: 4819 Registered: August 2001
|
Senior Member |
|
|
Here are a few examples:
where date_column >= '01-JAN-02';
-- the format of the date string must equal your nls_date_format (the default is 'DD-MON-YY') in order to use this syntax
where date_column >= to_date('01/01/2002', 'mm/dd/yyyy');
where date_column between to_date('01/01/2002', 'mm/dd/yyyy') and to_date('06/30/2002', 'mm/dd/yyyy');
where trunc(date_column) = trunc(sysdate - 1);
-- any entries for yesterday, trunc eliminates any time component from the comparison
|
|
|
Re: dates [message #39603 is a reply to message #39596] |
Thu, 25 July 2002 12:44 |
Leimbach
Messages: 2 Registered: July 2002
|
Junior Member |
|
|
'01-JAN-02' format worked! Thanks so much!
I have been struggling with this issue off and on for several weeks!
|
|
|