Re: Time Date formatting

We had a problem in the first set of data I sent out as I did not have both dates in the same row, however that did not change the outcome! But I now have the solution. You don't need to format it out at all before you subtract! By just subtracting the two dates without converting them or anything, it will give the time in between the two records in DAYS, by mutiplying by 24, I get it in hours:

SQL> ed
Wrote file afiedt.buf

  1 SELECT

  2     to_char(date1,'DD-MM-YYYY HH24:MI:SS') DATE1 ,
  3     to_char(date2,'DD-MM-YYYY HH24:MI:SS') DATE2,
  4     (DATE2-DATE1)*24
  5* from       date_table

------------------- ------------------- ----------------
11-08-2000 10:09:52 11-08-2000 11:09:52                1

My fault, instead of trying the simple solution first, I tried to go the hard way.

Thanks for your time.

Peter

>From: "Igor Neyman" <ineyman_at_perceptron.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: Time Date formatting
>Date: Thu, 10 Aug 2000 12:16:33 -0800
>
>First - subtract, then - convert:
>
>SELECT to_char((date2 - date1), 'mm/dd/yyyy hh24:mi:ss') FROM date_table;
>
>Igor Neyman, OCP DBA
>Perceptron, Inc.
>(734)414-4627
>ineyman_at_perceptron.com
>
>
>----- Original Message -----
>To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
>Sent: Thursday, August 10, 2000 2:45 PM
>
>
> > What I am trying to do is just take a date formatted out to the hour and
> > minute and subtract it from another date formatted out to the hour and
> > minute. Most of the dates I deal with are less than one day and I need
>to
> > know the difference in times between them. Here is my output:
> >
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 create table date_table
> > 2 (date1 date,
> > 3* date2 date)
> > SQL> /
> >
> > Table created.
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 insert into date_table
> > 2 (date1)
> > 3* values (sysdate)
> > SQL> /
> >
> > 1 row created.
> >
> > SQL> select to_char(date1,'DD-MON-YYYY HH24:MI:SS')
> > 2 from date_table;
> >
> > TO_CHAR(DATE1,'DD-MON-YYYYHH24:MI:SS')
> >
>--------------------------------------------------------------------------
>-
> > 06-AUG-2000 12:44:52
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 insert into date_table
> > 2 (date2)
> > 3* values (sysdate)
> > SQL> /
> >
> > 1 row created.
> >
> > SQL> commit;
> >
> > Commit complete.
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_char(date2,'DD-MON-YYYY
> > HH24:MI:SS')-to_char(date1,'DD-MON-YYYY HH24:MI:SS')
> > 2* from date_table
> > SQL> /
> > ERROR:
> > ORA-01722: invalid number
> >
> >
> >
> > no rows selected
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_date(date2,'DD-MON-YYYY
> > HH24:MI:SS')-to_date(date1,'DD-MON-YYYY HH24:MI:SS')
> > 2* from date_table
> > SQL> /
> > ERROR:
> > ORA-01840: input value not long enough for date format
> >
> >
> >
> > no rows selected
> >
> > SQL> select to_char(date1,'DD-MON-YYYY HH24:MI:SS')
> > 2 from date_table;
> >
> > TO_CHAR(DATE1,'DD-MON-YYYYHH24:MI:SS')
> >
>--------------------------------------------------------------------------
>-
> > 06-AUG-2000 12:44:52
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_char(date2,'DD-MON-YYYY HH24:MI:SS')
> > 2* from date_table
> > SQL> /
> >
> > TO_CHAR(DATE2,'DD-MON-YYYYHH24:MI:SS')
> >
>--------------------------------------------------------------------------
>-
> >
> > 06-AUG-2000 12:46:13
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_date(date2,'DD-MON-YYYY HH24:MI:SS')
> > 2* from date_table
> > SQL> /
> > ERROR:
> > ORA-01840: input value not long enough for date format
> >
> >
> >
> > no rows selected
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_char(to_date(date2,'DD-MON-YYYY
> > HH24:MI:SS'))-to_char(to_date(date1,'DD-MON-YYYY HH24
> > 2* from date_table
> > SQL> /
> > ERROR:
> > ORA-01840: input value not long enough for date format
> >
> >
> >
> > no rows selected
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_date(to_char(date2,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY
> > HH24:MI:SS'))-to_date(to_ch
> > 2* from date_table
> > SQL> /
> > select to_date(to_char(date2,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY
> > HH24:MI:SS'))-to_date(to_char(da
> >
> > *
> > ERROR at line 1:
> > ORA-00923: FROM keyword not found where expected
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_date(to_char(date2,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY
> > HH24:MI:SS')-to_date(to_cha
> > 2* from date_table
> > SQL> /
> >
> >
>TO_DATE(TO_CHAR(DATE2,'DD-MON-YYYYHH24:MI:SS'),'DD-MON-YYYYHH24:MI:SS')-TO_D
>ATE(TO_CHAR(DATE1,'DD-MO
> >
>--------------------------------------------------------------------------
>--------------------------
> >
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 insert into date_table
> > 2 (date1)
> > 3* values (to_date(sysdate )
> > SQL> /
> > values (to_date(sysdate )
> > *
> > ERROR at line 3:
> > ORA-00917: missing comma
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 insert into date_table
> > 2 (date1)
> > 3* values (to_date(sysdate,'DD-MON-YYYY HH24:MI:SS'))
> > SQL> /
> > insert into date_table
> > *
> > ERROR at line 1:
> > ORA-01840: input value not long enough for date format
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 insert into date_table
> > 2 (date1)
> > 3* values (to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'))
> > SQL> /
> > values (to_char(sysdate,'DD-MON-YYYY HH24:MI:SS'))
> > *
> > ERROR at line 3:
> > ORA-01830: date format picture ends before converting entire input
>string
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 insert into date_table
> > 2 (date1)
> > 3 values (
> > 4* (sysdate,'DD-MON-YYYY HH24:MI:SS'))
> > SQL>
> > SQL>
> > SQL>
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_char(date1,'DD-MON-YYYY
> > HH24:MI:SS'),to_char(date2,'DD-MON-YYYY HH24:MI:SS')
> > 2* from date_table
> > SQL> /
> >
> > TO_CHAR(DATE1,'DD-MON-YYYYHH24:MI:SS')
> >
>--------------------------------------------------------------------------
>-
> > TO_CHAR(DATE2,'DD-MON-YYYYHH24:MI:SS')
> >
>--------------------------------------------------------------------------
>-
> > 06-AUG-2000 12:44:52
> >
> >
> >
> > 06-AUG-2000 12:46:13
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_date(to_char(date2,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY
> > HH24:MI:SS')-
> > 2 to_date(to_char(date1,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY
> > HH24:MI:SS')
> > 3* from date_table
> > SQL> /
> >
> >
>TO_DATE(TO_CHAR(DATE2,'DD-MON-YYYYHH24:MI:SS'),'DD-MON-YYYYHH24:MI:SS')-TO_D
>ATE(TO_CHAR(DATE1,'DD-MO
> >
>--------------------------------------------------------------------------
>--------------------------
> >
> >
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_date(to_char(date2,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY
> > HH24:MI:SS')-
> > 2 to_date(to_char(date1,'DD-MON-YYYY HH24:MI:SS'),'DD-MON-YYYY
> > HH24:MI:SS')
> > 3* from date_table
> > SQL>
> > SQL> /
> >
> >
>TO_DATE(TO_CHAR(DATE2,'DD-MON-YYYYHH24:MI:SS'),'DD-MON-YYYYHH24:MI:SS')-TO_D
>ATE(TO_CHAR(DATE1,'DD-MO
> >
>--------------------------------------------------------------------------
>--------------------------
> >
> >
> >
> > SQL> select to_date(sysdate,'DD-MON-YYYYHH24:MI:SS')
> > 2 from dual;
> > ERROR:
> > ORA-01840: input value not long enough for date format
> >
> >
> >
> > no rows selected
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select to_char(sysdate,'DD-MON-YYYYHH24:MI:SS')
> > 2* from dual
> > SQL> /
> >
> > TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')
> >
>--------------------------------------------------------------------------
>-
> > 06-AUG-200013:05:23
> >
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select
> >
>to_date(to_char(sysdate,'DD-MON-YYYYHH24:MI:SS'),'DD-MON-YYYYHH24:MI:SS')
> > 2* from dual
> > SQL> /
> >
> > TO_DATE(T
> > ---------
> > 06-AUG-00
> >
> > SQL>
> > SQL> ed
> > Wrote file afiedt.buf
> > 1 select
> >
>to_date(to_char(sysdate,'DD-MON-YYYYHH24:MI:SS'),'DD-MON-YYYYHH24:MI:SS')
> > 2* from dual
> > SQL>
> > SQL> /
> >
> > TO_DATE(T
> > ---------
> > 06-AUG-00
> >
> > SQL>
> >
> >
> >
> > If there is anyone out there who can help me with this, I would really
> > appreciate it.
> >
> > Peter Hazelton
> >
> > ________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
> >
> > --
> > Author: Peter Hazelton
> > INET: peterhazelton_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
>
>--
>Author: Igor Neyman
> INET: ineyman_at_perceptron.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
Received on Fri Aug 11 2000 - 09:03:20 CDT