| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Time Date formatting
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
> 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
> --------------------------------------------------------------------------
--------------------------
ATE(TO_CHAR(DATE1,'DD-MO
> --------------------------------------------------------------------------
--------------------------
ATE(TO_CHAR(DATE1,'DD-MO
> --------------------------------------------------------------------------
--------------------------
 |
 |