Re: Dates - Just shoot me.

select to_char(action_date,'dd-mm-yyyy hh24:mi:ss') from
service_history
/
Look Ma, I have a time in...

Pang :),

Sybrand Bakker, Oracle DBA

Moore <rlmoore_at_purdue.edu> wrote in message news:Pine.SOL.4.10.10003201420480.29894-100000_at_herald.cc.purdue.edu...
> 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
> > >
> > >
> >
> >
> >
>
Received on Mon Mar 20 2000 - 13:44:19 CST