Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Time Date formatting

RE: Time Date formatting

From: Jain, Akshay <Akshay.Jain_at_cit.com>
Date: Fri, 11 Aug 2000 16:44:59 -0400
Message-Id: <10586.114415@fatcity.com> 





I will see your detail and raise you an answer. :-)



The numeric values from dates seem to be in the units of days.  There
is retention of decimal values to show part of a day (hours, minutes, etc.)




e.g.:



16:45:23 SQL> l


  1* select to_char(d,'DD MM YYYY HH24:MI:SS')
  2 , c


  3* from temp2



TO_CHAR(D,'DDMMYYYY C



------------------- ----------


11 08 2000 16:45:47 1
11 08 2000 16:45:54 2
11 08 2000 16:46:04 3





 real: 100



16:45:56 SQL> select 24 * 60 * 60 * (t2.d - t1.d) from temp2 t1, temp2 t2
16:46:23   2   where t2.c = 2
16:46:23   3   and t1.c = 1
16:46:25   4  
16:46:25 SQL> /





24*60*60*(T2.D-T1.D)



                   7







16:49:08 SQL> l


  1  select 24 * 60 * 60 * (t2.d - t1.d) from temp2 t1, temp2 t2
  2   where t2.c = 2


  3*  and t1.c =3


16:49:09 SQL> 



24*60*60*(T2.D-T1.D)



                 -10





 real: 80


16:46:54 SQL> 



Akshay Jain




Newcourt-CIT


Tel. (416) 507-5385


mailto:Akshay.Jain_at_cit.com                      






-----Original Message-----



From: Peter Hazelton [mailto:peterhazelton_at_hotmail.com]
Sent: Thursday, August 10, 2000 2:45 PM


To: Multiple recipients of list ORACLE-L
Subject: Time Date formatting




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


--------------------------------------------------------------------



Received on Fri Aug 11 2000 - 15:44:59 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US