Home » SQL & PL/SQL » SQL & PL/SQL » Oracle date comparison issue (Oracle DB 10g)
Oracle date comparison issue [message #294211] Wed, 16 January 2008 17:53 Go to next message
tllocke
Messages: 22
Registered: March 2006
Junior Member
I'm sure someone will find this easy; I'm hoping so anyway! I've been given the task of writing reports from an old Visual Basic app that has just been updated to use an Oracle 10 backend (used to be SQL Server). The VB app passes in the date to be used in my query with a completely different format then the dates in the database. All I need to do is do a comparison in the where. If anyone can give me a solution to this.. it would make my week:

SELECT e.*
FROM tabEmployees e
WHERE e.startdate >= to_date('2008-01-16 3:40:53 PM', 'YYYY-MM-DD HH:MM:SS')

The long date format is what's passed in, but I only want to compare the date part of the string, not the entire time portion as well. I'm thinking this has to be a common thing.

Any help is greatly appreciated. Thanks.
TL
Re: Oracle date comparison issue [message #294286 is a reply to message #294211] Thu, 17 January 2008 01:57 Go to previous message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
TRUNC function removes time portion; here's an example:
SQL> select to_date('2008-01-16 3:40:53 PM', 'yyyy-mm-dd hh:mi:ss PM') r
  2  from dual;

R
-------------------
16.01.2008 15:40:53

SQL> select trunc(to_date('2008-01-16 3:40:53 PM', 'yyyy-mm-dd hh:mi:ss PM')) r
  2  from dual;

R
-------------------
16.01.2008 00:00:00

SQL>

If table values also contain time, you might need to use TRUNC with 'e.startdate' column as well.

Also, note that you've used a wrong format mask: 'MM' is used for 'months'; 'MI' is used for minutes:
SQL> select to_date('2008-01-16 3:40:53 PM', 'YYYY-MM-DD HH:MM:SS') r
  2  from dual;
select to_date('2008-01-16 3:40:53 PM', 'YYYY-MM-DD HH:MM:SS') r
                                        *
ERROR at line 1:
ORA-01810: format code appears twice
Furthermore, if you insist on PM notation, you'll have to include it into the TO_DATE function (as in my previous example):
SQL> select to_date('2008-01-16 3:40:53 PM', 'YYYY-MM-DD HH:Mi:SS') r
  2  from dual;
select to_date('2008-01-16 3:40:53 PM', 'YYYY-MM-DD HH:Mi:SS') r
               *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Finally, this is how your query might look like:
SELECT e.*
FROM tabEmployees e
WHERE e.startdate >= TRUNC(TO_DATE('2008-01-16 3:40:53 PM', 'YYYY-MM-DD HH:MI:SS PM'));
Previous Topic: creating a query showing file not existing
Next Topic: Definite Indexing scenarios
Goto Forum:
  


Current Time: Sun Dec 04 02:30:16 CST 2016

Total time taken to generate the page: 0.08277 seconds