Home » SQL & PL/SQL » SQL & PL/SQL » dates
dates [message #39596] Thu, 25 July 2002 08:26 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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!
Previous Topic: Optimize query
Next Topic: COPY command in SQL giving some error
Goto Forum:
  


Current Time: Thu Mar 28 04:25:19 CDT 2024