Skip to Main Content

Java Database Connectivity (JDBC)

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to match time alone (without date) in a query

sperkmandlFeb 14 2011 — edited Feb 15 2011
Hi, I have a date colum named timeprop, where a time value has been inserted through:

DateFormat.getTimeInstance(DateFormat.MEDIUM, "de_DE").parse("11:12:13")

In SQL developer I can check the inserted value as:

select timeprop from version_table where timeprop is not null;

getting:

TIMEPROP
-------------------------
01.01.1900 11:12:13

so I guess Oracle added a default date as 1.01.1900. Ok.
But if I run:

select timeprop from version_table where timeprop = to_date('11:12:13', 'hh24:mi:ss');

then I get no result. I succeed only after adding that default date to the query - by patching the format as well - e.g.

select timeprop from version_table where timeprop = to_date('1.01.1900 11:12:13', 'dd.mm.yyyy hh24:mi:ss');

Basically, no time alone in a query.
Is this transformation always needed or am I missing anything from this game ?
Server is 11gr1.
Thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 15 2011
Added on Feb 14 2011
11 comments
1,361 views