Re: Dates - Just shoot me.

From: Parker Barrington <Parker.Barrington_at_Dal.Ca>
Date: Mon, 20 Mar 2000 19:19:05 GMT
Message-ID: <38d67952.11175819@news.dal.ca>

It's probably has the time component of the date stored as well. Try

trunc(action_date) = '16-MAR-00'

>
>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
>
>
Received on Mon Mar 20 2000 - 13:19:05 CST