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  |
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  |
 |
Littlefoot
Messages: 21825 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'));
|
|
|
Goto Forum:
Current Time: Wed Jul 16 05:56:24 CDT 2025
|