| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Dates
LIKE only compares character-type expressions, so Oracle does a conversion of
the date-type column to a character string in the same format you've used,
leaving off time-of-day. Since you have data for that day, but not at midnight
at the start of that day, you find two rows with matching whole dates, as if
you'd said (avoiding the implicit type conversion, as is good practice) "WHERE
TRUNC(NC_DATE_CLS) = TO_DATE('25-JUN-04')". In the second query, Oracle
converts the date string to a date-time for that day at the midnight at the
start of that day, but you have no data at that specific second of that day, so
you get no rows. The third query likewise finds a range of time that only
covers the single second at midnight at the start of that day, but the fourth
query actually finds a *2-day-plus-one-second* date range that includes the
entire day of the 24th, the entire day of the 25th, and the single second at
midnight of the 26th, so you still get the couple of rows that the second query
gave you, but you could also get more. Since you evidently have no data for the
extra day+1-second, however, you get no extra rows.
Yours,
Dan Tow
650-858-1557
www.singingsql.com
Quoting Oracle <oracle_list_at_hotmail.com>:
> Hi
>
> Can any please explain the reason for the differing results in the folowing
> queries
>
> 1)
> SELECT NCSN, NC_DATE_CLS FROM NONCONFORM WHERE NC_DATE_CLS LIKE '25-JUN-04'
>
>
> NCSN NC_DATE_CLS
> --------- ---------
> 3298 25-JUN-04
> 3299 25-JUN-04
>
> 2)
> SELECT NCSN, NC_DATE_CLS FROM NONCONFORM WHERE NC_DATE_CLS = '25-JUN-04'
>
> no rows selected
>
>
> 3)
> SELECT NCSN, NC_DATE_CLS FROM NONCONFORM WHERE NC_DATE_CLS between
> '25-JUN-04' AND '25-JUN-04';
>
> no rows selected
>
> 4)
> SELECT NCSN, NC_DATE_CLS FROM NONCONFORM WHERE NC_DATE_CLS between
> '24-JUN-04' AND '26-JUN-04'
>
> NCSN NC_DATE_CLS
> --------- ---------
> 3298 25-JUN-04
> 3299 25-JUN-04
>
>
> Table nonconform....columns....NCSN NUMBER, NC_DATE_CLS DATE......
> Oracle 8.1.7
>
> Thanks
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jun 25 2004 - 10:50:53 CDT
 |
 |