| 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 -> Re: Dates - Just shoot me.
How would I know that?
SQL> desc service_history
Name Null? Type ------------------------------- -------- ---- PREM_CODE NOT NULL VARCHAR2(7) SERV_NUM NOT NULL NUMBER(4) CUST_CODE NOT NULL NUMBER(9) STYP_CODE NOT NULL VARCHAR2(4) SCAT_CODE NOT NULL VARCHAR2(4) ACTN_CODE NOT NULL VARCHAR2(4) ACTION_DATE NOT NULL DATE ACTIVITY_DATE NOT NULL DATE
SQL> select action_date from service_history;
action_da
01-SEP-99 03-AUG-99 01-JUL-99 02-JUN-99 03-MAY-99 01-APR-99 03-MAR-99 02-FEB-99 04-JAN-99 02-DEC-98 30-OCT-98 01-OCT-98 01-SEP-98 03-AUG-98
On Mon, 20 Mar 2000, Parker Barrington wrote:
> 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
> >
 |
 |