| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Dates - Just shoot me.
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:03:40 CST
 |
 |