OraFAQ Forum: SQL & PL/SQL » Oracle date comparison issue

Home » SQL & PL/SQL » SQL & PL/SQL » Oracle date comparison issue (Oracle DB 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Oracle date comparison issue [message #294286 is a reply to message #294211]

Thu, 17 January 2008 01:57

Littlefoot
Messages: 21824
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'));

Report message to a moderator

Previous Topic:	creating a query showing file not existing
Next Topic:	Definite Indexing scenarios

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 06 19:59:19 CDT 2025