to_date function

[ofsouto]

I created a table and I inserted a record.

ORACLE 8i - 8.1.7
NLS_DATE_FORMAT = 'DD/MM/RR'

CREATE TABLE DATE_TEST (
DAY DATE,
NAME VARCHAR2 (20) ) ;

INSERT INTO DATE_TEST VALUES (SYSDATE, 'JOHN');

Why the next three queries get results and the others not?

SELECT * FROM DATE_TEST WHERE TO_DATE(DAY) = TO_DATE(SYSDATE) SELECT * FROM DATE_TEST WHERE TO_DATE(DAY) = TO_DATE('28/02/2003','DD/MM/YYYY')
SELECT * FROM DATE_TEST WHERE TO_DATE(DAY) = '28/02/2003'

SELECT * FROM DATE_TEST WHERE DAY = TO_DATE(SYSDATE)
SELECT * FROM DATE_TEST WHERE DAY = TO_DATE('28/02/2003','DD/MM/YYYY')
SELECT * FROM DATE_TEST WHERE DAY = '28/02/2003'

Must I use TO_DATE(DATE TYPE COLUMN) in all queries?
Thanks in advance.

 

[fmorel]

It is because date type include Hours minutes and seconds.
Sysdate is a function that returns system date in date format (ie including hours mins and secs)
to_date() function converts a string into a date.
So when you do to_date(sysdate), oracle first converts (implicitly) sysdate to a string using your NLS format (DD/MM/RR) and then converts it to a date (to_date) using your NLS format again.
So to_date(sysdate) truncates sysdate (to 00:00:00).

To verify, try a
select to_char(day,'dd/mm/yyyy hh24:mi:ss'), to_char(to_date(sysdate),'dd/mm/yyyy hh24:mi:ss') from date_test;

 

[jee]

Expanding on what fmorel said. If you want to extract items from the table using a specified date and you don't care about the time component, your where clause would look something like this:

WHERE trunc (day) = to_date ('12/01/2003', 'MM/DD/YYYY);

You need to use the trunc (truncate) function to remove the time component from your stored dates.

 

[AOLEURO]

Hi

It is not a good idea to use TRUNC() or change column values in WHERE clause - because Oracle has to make a full table scan.

Only use such statements when they are used by few users and infrequently used. Else TRUNC() on inserting rows, separate date and time in columns or make a FUNCTION INDEX.

Regards
Allan

 

[THoey]

From what I have been told, and I could be wrong, you can use the TRUNC() function as long as you don't use it on the table side. An example would be:

WHERE day >= TRUNC(SYSDATE - 1) and
day <= TRUNC(SYSDATE); Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[AOLEURO]

Hi Terry

It is correct - but Ofsouto forgot to TRUNC() on inserting - and as I wrote - use TRUNC() on inserting columns so you avoid TRUNC in WHERE clause on columns.

In your case (combined with Ofsouto) it have to look like this:

WHERE day >= TRUNC(sysdate - 1)
and day < trunc( sysdate + 1 )

(It has no effect to try BETWEEN xxx AND yyyy, because Oracle translate it to and >= and <= in the optimizer)

Depending on index Oracle makes a RANGE SCAN.
Regards
Allan
Icq: 346225948

 

[sfvb]

If the time portion of the date you need isn't important, then I would do as Allan has suggested, truncate the time portion of the date before inserting.

If, however, it is important, then I would add an index to the date column, and query like this:

SELECT   * 
FROM     date_test 
WHERE    day BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE) + 86399/86400;

or 

SELECT   * 
FROM     date_test 
WHERE    day BETWEEN TO_DATE('28/02/2003','DD/MM/YYYY') AND TO_DATE('28/02/2003','DD/MM/YYYY') + 86399/86400;

From my timed tests, this is the most efficient way to query on a timestamp, when the you only care about the date.

In case you're wondering about the fraction:
86399 = 24 * 60 * 60 - 1 (one second less than a full day)
86400 = 24 * 60 * 60 (a full day)

Steve

 

[jimbopalmer]

Quite a lot of my PL/SQL has the global constant g_almost_a_day = 86399/86400; defined at the top.

My application had truncated dates so a person is a manager til 8/13 and becomes a general manager on 8/14, and finds they cannot approve POs, or timecards for 24 hours, by adding g_almost a day to my end dates in my between clauses, I make my managers happy again. I tried to remain child-like, all I acheived was childish.