Re: Dates - Just shoot me.

Moore <rlmoore_at_purdue.edu> wrote in message news:Pine.SOL.4.10.10003201359230.28191-100000_at_herald.cc.purdue.edu...
>
> The first query returns all records for a given ID.
> The second, third, fourth and fifth query are attempts to figure out how
> in God's name Oracle handles dates.
>
> Do I realy have to include all the work in the sixth query just to return
> a date?
>
> I know for a fact that this issue is far less complex in SQL Server.
>
> 1 SELECT action_date,
> 2 type_code
> 3 FROM service_history
> 4 WHERE cust_code = '287541'
> 5 AND prem_code = '240168'
> 6 ORDER BY action_date;
>
>
> ACTION TYPE
> --------- ----
> 18-JAN-00 E

> 16-FEB-00 E
> 16-MAR-00 E
>
> 3 rows selected.
>
> SQL> SELECT cust_code custcode,
> 2 prem_code premcode,
> 3 action_date,
> 4 type_code
> 5 FROM service_history
> 6 WHERE cust_code = '287541'
> 7 AND prem_code = '240168'
> 8 AND action_date = TO_DATE('16-MAR-00', 'DD-MON-YY')
> 9 /
>
> no rows selected
>
> real: 30
> SQL> SELECT cust_code custcode,
> 2 prem_code premcode,
> 3 action_date,
> 4 type_code
> 5 FROM service_history
> 6 WHERE cust_code = '287541'
> 7 AND prem_code = '240168'
> 8 AND action_date = TO_DATE('16-MAR-2000', 'DD-MON-YYYY')
> 9 /
>
> no rows selected
>
> SQL> SELECT cust_code custcode,
> 2 prem_code premcode,
> 3 action_date,
> 4 type_code
> 5 FROM service_history
> 6 WHERE cust_code = '287541'
> 7 AND prem_code = '240168'
> 8 AND action_date = TO_DATE('16-MAR-1900', 'DD-MON-YYYY')
> 9 /
>
> no rows selected
>
> SQL> SELECT cust_code custcode,
> 2 prem_code premcode,
> 3 action_date,
> 4 type_code
> 5 FROM service_history
> 6 WHERE action_date = '16-MAR-00';
>
> no rows selected
>
> SQL> SELECT cust_code custcode,
> 2 prem_code premcode,
> 3 action_date,
> 4 type_code
> 5 FROM service_history
> 6 WHERE cust_code = '287541'
> 7 AND prem_code = '240168'
> 8 AND TO_DATE(TO_CHAR(action_date, 'DD-MON-YYYY'), 'DD-MON-YYYY') =
> TO_DATE('16-MAR-2000', 'DD-MON-YYYY');
>
> CUSTCODE PREMCOD ACTION_DA TYPE
> --------- ------- --------- ----
> 287541 240168 16-MAR-00 E
>
>

The issue here probably is you have a time included in the your dates. With the last query you are effectively masking the time of. If you don't need the time, you should take care it doesn't reach the table (by TRUNCing it upon entry).
If you do need the time you should either trunc(date_column,'DD-MON-YYYY') = trunc('date_literal','DD-MON-YYYY') which has as usual disadvantage it can suppress an index or
you need to do something like
date_column between to_date('yourdate') and to_date('yourdate') + 1 - 1/3600

I admit both solutions are a pain too, but they work. I started using them when we needed to capture the exact transmittal time of radio and television commercials, as the rates were time dependent. Augh!

I know it is very easy to get a gun in your country (I visited a Sports Authority in PA once), yet this time I'm not in the mood anymore to shoot American citizens (yes, I did work with Americans in the past, who were driving me just crazy)

Hth,

Sybrand Bakker, Oracle DBA Received on Mon Mar 20 2000 - 13:21:39 CST