Usage of to_date function

[HuntoB]

Hi,

Forgive my ignorance but I can not understand why the following statement doesn't work.

This statment

SELECT booking_id, destination, trip_date
FROM booking;

selected all the bookings contained in the database. I'm trying to create a statement that will bring up a record according to an entered date and frist tried this

select booking_id, destination, trip_date
from booking
where trip_date = to_date('5-JUL-03');

and this

select booking_id, destination, trip_date
from booking
where trip_date = ('5-JUL-03','DD-MON-YY);

and finally this

select booking_id, destination, trip_date
from booking
where trip_date = '05-JUL-03';

yet none of them selected any rows.

Can anyone help? Thank you in advance.

 

[sem]

First of all what is trip_date data type? Is it DATE? Even if so, using MON submask may depend on your NLS_LANG session parameter. Then, if you don't get an error but rather simply no rows are returned, according to YY submask '03' is evaluated as '0003'. This may also be a problem. If you need to use 2-digit year 'RR' is probably what you need.

Regards, Dima

 

[jad]

matters how 'trip_date' was entered ... but if it was entered in in a specific locale, or was entered in from sysdate (or another date function that doesn't necessarily set the time of the day to 00:00:00) you might be better of using TRUNC or ROUND

select booking_id, destination, trip_date
from booking
where trunc(trip_date) = to_date('05-July-2003');

 

[sem]

Jad, if this field of DATE datatype, it doesn't matter in which locale it was enterd, as dates are stored in locale-independent format. As for using TRUNC(), in some cases it's preferably (though less convenient) to use BETWEEN, as TRUNC eliminates possible index usage.

Regards, Dima

 

[jad]

i've never played with it big time ... but i know that if you are using web forms and you are using javascript checking to create a date then that uses the users locale at the browser end to set the time and date, i've never played enough to know if it stores any different if using normal pl/sql.

i put it in as a possibility, if dates have been created with to_date when inserted and they don't use the time part of it, then there should be no trouble with what he's doing, but if there is anything that relies on real time it won't match even if it's 1/100th of a second out.

 

[sem]

Jad, FYI: DATE is stored within Oracle as 8 byte binary and its precision is seconds; TO_DATE, depending on the format mask passed, may create seconds also.

Regards, Dima

 

[jad]

certainly it _might_ ... hence the 'and they don't use the time part of it' in my previous message, i.e. they don't use the time part of the formatting mask.

I've got the date down as a 7 byte construct ... one for each component Century through second, but yeah i know it's to the second, got mixed up with the 'J.SSSSS' construct.

 

[sem]

The 8th byte is 0. You may execute select dump(sysdate) from dual

Regards, Dima