Home » Developer & Programmer » Forms » How to convert the number to time and time back to number?
How to convert the number to time and time back to number? [message #294416] Thu, 17 January 2008 09:17 Go to next message
msuphaluk
Messages: 20
Registered: December 2007
Location: BKK,Thailand
Junior Member

Dear All,
I have a question about how to convert the number to time and time back to number in oracle form?

Because in my form, it has a time datatype column. When it displayes on form. It shows the timing as well but in DB it's stored in number format. My question is how to convert the number to time and time to number. I wanna display this field in my report as a time format.

Regards,
Suphaluk M.
Re: How to convert the number to time and time back to number? [message #294494 is a reply to message #294416] Thu, 17 January 2008 15:41 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
in DB it's stored in number format
As you didn't provide enough information, I'll have to guess. As you've put it, it means that datatype of this column is NUMBER. The only way that makes sense is a Julian date. It is the number of days since 01 January 4713 BC. For example, a Julian date of 2454483 represents today (17 January 2008):
SQL> alter session set nls_date_Format = 'dd.mm.yyyy bc';

Session altered.

SQL> select trunc(sysdate) - 2454482 from dual;

TRUNC(SYSDATE)
--------------
01.01.4712 pne

SQL>
("pne" is "BC" in Croatian; I've added one day to avoid illegal "0" as the result).

Therefore, I'd say that dates in your table are stored as Julian dates. This is how you'd store them into a table:
SQL> create table test (col number);

Table created.

SQL> insert into test (col) values
  2  (to_char(to_date('30.09.2000', 'dd.mm.yyyy'), 'J'));

1 row created.

SQL> select col from test;

             COL
----------------
         2451818
This is how you'd get it back as a DATE:
SQL> select to_date(col, 'J') from test;

TO_DATE(CO
----------
30.09.2000

SQL>

Basically, you'll have to research Julian dates and date format masks; that should be all you need.
Re: How to convert the number to time and time back to number? [message #294510 is a reply to message #294416] Thu, 17 January 2008 18:30 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Search this forum for 'datetime' and you will find entries like http://www.orafaq.com/forum/m/281189/67467/?srch=datetime#msg_281189

David
Re: How to convert the number to time and time back to number? [message #294544 is a reply to message #294494] Thu, 17 January 2008 23:50 Go to previous messageGo to next message
msuphaluk
Messages: 20
Registered: December 2007
Location: BKK,Thailand
Junior Member

Dear All,
Thank you for your promptly replay and sorry to post p/b unclear.

Let me explain my p/b. I have a field called "check_start_time" in my form. The data type in form is "Time" but in database, datatype of this field is number. When I enter 07:00 in form and it will insert "25200" into database.

My question is how to convert the "25200", number format to "07:00", datetime format and conver "07:00:00" to "25200".

Thank you in advanced for your kindly information,

Best Regards,
Suphaluk M.
Re: How to convert the number to time and time back to number? [message #294564 is a reply to message #294544] Fri, 18 January 2008 00:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What is the algorithm that translates '07:00' to '25200'?
Re: How to convert the number to time and time back to number? [message #294642 is a reply to message #294416] Fri, 18 January 2008 05:00 Go to previous messageGo to next message
msuphaluk
Messages: 20
Registered: December 2007
Location: BKK,Thailand
Junior Member

Dear,
Correct, this is exactly my current p/b.

Regards,
Suphaluk M.
Re: How to convert the number to time and time back to number? [message #294656 is a reply to message #294642] Fri, 18 January 2008 06:06 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So, did you check forms' triggers? Is it in there? Did you check database triggers? *Something* is translating these values!

I'd say that numeric value represents number of seconds between midnight and time stored in a column. In your example, it would be
7 hours x 60 minutes x 60 seconds = 25200
Now it's your turn.
Re: How to convert the number to time and time back to number? [message #294686 is a reply to message #294416] Fri, 18 January 2008 08:03 Go to previous messageGo to next message
msuphaluk
Messages: 20
Registered: December 2007
Location: BKK,Thailand
Junior Member

Dear,
Thank a lot for your suggestion. I will find the exactly formula and keep you posted.

Note: I have checked in program but there is no any formula or db trigger defined for this case.

Regards,
Suphaluk M.
Re: How to convert the number to time and time back to number? [message #295232 is a reply to message #294656] Mon, 21 January 2008 19:14 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please post the code that is populating your field.

David
Re: How to convert the number to time and time back to number? [message #297507 is a reply to message #295232] Fri, 01 February 2008 03:18 Go to previous messageGo to next message
msuphaluk
Messages: 20
Registered: December 2007
Location: BKK,Thailand
Junior Member

Dear David,
Sorry to very lately reply. Below is a code that I used to solve my issue.

Regards,
Suphaluk M
BKK, Thailand.

  FUNCTION convert_number_to_time (
    p_time  IN NUMBER
  )
  RETURN VARCHAR2  
  IS 
   ln_time  NUMBER;
   ln_hour  NUMBER;
   ln_min   NUMBER;
   ln_sec   NUMBER;  
  BEGIN
    ln_hour := TRUNC(p_time/3600);
    ln_time := p_time - (ln_hour * 3600);
    -- 
    IF ln_time = 0 THEN
      ln_min := 0;
      ln_sec := 0;
    ELSE 
      ln_min  := TRUNC(ln_time/60);
      ln_time := ln_time - (ln_min * 60); 
      ln_sec  := ln_time;
    END IF;
    --
    RETURN  LTRIM(TO_CHAR(ln_hour,'00')) 
            || ':' || LTRIM(TO_CHAR(ln_min,'00'))
            || ':' || LTRIM(TO_CHAR(ln_sec,'00'));
  END convert_number_to_time;
  --
  FUNCTION convert_time_to_number (
    p_time  IN VARCHAR2
  ) 
  RETURN NUMBER 
  IS
   lv_time  VARCHAR2(10);
   lv_hour  VARCHAR2(2);
   lv_min   VARCHAR2(2);
   lv_sec   VARCHAR2(2);
   --
   ln_out   NUMBER;
  BEGIN
    lv_time := p_time;
    --
    -- Find hous
    --
        lv_hour := SUBSTR(lv_time,1,INSTR(lv_time,':')-1);
    lv_time := SUBSTR(lv_time,INSTR(lv_time,':')+1);
    --
    -- Find Minute
    --
    lv_min  := SUBSTR(lv_time,1,INSTR(lv_time,':')-1);
    lv_time := SUBSTR(lv_time,INSTR(lv_time,':')+1);    
    --
    -- Find second
    --
    lv_sec  := lv_time;
    --
    --dbms_output.put_line('Hour : ' || lv_hour);
    --dbms_output.put_line('Min : ' || lv_min);
    --dbms_output.put_line('Sec : ' || lv_sec);        
    --
    RETURN (NVL(TO_NUMBER(lv_hour),0) * 3600) + 
           (NVL(TO_NUMBER(lv_min),0) * 60) + 
           (NVL(TO_NUMBER(lv_sec),0));
  END convert_time_to_number;

Upd mod: Add 'codes' tags.

[Updated on: Sun, 03 February 2008 22:44] by Moderator

Report message to a moderator

Re: How to convert the number to time and time back to number? [message #297819 is a reply to message #297507] Sun, 03 February 2008 22:48 Go to previous message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Thanks for posting it.

David
Previous Topic: how to moineteing the updating of data
Next Topic: Internet Explorer Problem While Accessing to Application Server
Goto Forum:
  


Current Time: Thu Dec 08 16:28:38 CST 2016

Total time taken to generate the page: 0.04923 seconds