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: Rajagopal Venkataramany <rajagopalvr_at_excite.com>
Date: Fri, 11 Aug 2000 07:33:44 -0700 (PDT)
Message-Id: <10586.114360@fatcity.com>


Hi,

  I feel there is something fundamentally wrong here.

  In the way u are inserting details, there would be rows where   either one of the date columns will be null. So it does not   make sense when u try to find the difference in days between   them when we know that one of the date fields will have a null   value.(Going by what u have illustrated in your email message).

SQL>select * from vrr;

DATE1 DATE2

--------- ---------
10-AUG-00
09-AUG-00
          08-AUG-00
          07-AUG-00

SQL>select date1 - date2 from vrr;

   DATE1-DATE2


SQL>   Hope u agree with me...

Regards
Rajagopal Venkataramany

On Thu, 10 Aug 2000 10:45:13 -0800, ORACLE-L_at_fatcity.com wrote:

> 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_DATE(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_DATE(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_DATE(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
Received on Fri Aug 11 2000 - 09:33:44 CDT

Original text of this message

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