Home » SQL & PL/SQL » SQL & PL/SQL » Convert string to time of day (forms 6i)
Convert string to time of day [message #402325] Fri, 08 May 2009 14:56 Go to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member
Hello everyone,

I am having a problem with the correct time showing in a table. When the time is entered on the form, for example 11:24 when it saves to the table it saves as 41040. How can I convert this back to 11:24 without changing the data type in the table which by the way is of Varchar. Another example is 08:00 converts to 28800. Is this seconds? I dont understand this conversion, is there a math equation I can use to convert it back to real time.

Report message to a moderator

Re: Convert string to time of day [message #402328 is a reply to message #402325] Fri, 08 May 2009 15:14 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
That's the penalty you pay when you store a DATE (time) in a VARCHAR.
That's what you deserve when rely on implicit conversion by having a form text field be a different datatype than the table column.

Here's a hint:


28800 is 1/3 of a day (in seconds).

[Updated on: Fri, 08 May 2009 15:19]

Report message to a moderator

Re: Convert string to time of day [message #402329 is a reply to message #402328] Fri, 08 May 2009 15:30 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member
First of all Im not the one that did it and second, your hint does not help me.

Can anyone else help, if i use the time datatype in a form it automatically converts it to real time, I am trying to figure out a way to do the samething in SQL.

Report message to a moderator

Re: Convert string to time of day [message #402332 is a reply to message #402329] Fri, 08 May 2009 15:45 Go to previous messageGo to next message
Littlefoot
Messages: 21825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Oh, poor you. Simple mathematics is beyond your knowledge?

41040 = seconds

To convert seconds to minutes, divide this value by 60 (because every minute has 60 seconds):
41040 / 60 = 684 minutes

To convert minutes to hours, divide this value by 60 (because every hour has 60 minutes):
684 / 60 = 11.4

This is "11 hours and several minutes".

Now take decimal part and convert it back to minutes - this time by multiplying:
(11.4 - 11) * 60 = 0.4 * 60 = 24 minutes

The final result is: 41040 seconds = 11 hours and 24 minutes

Report message to a moderator

Re: Convert string to time of day [message #402333 is a reply to message #402329] Fri, 08 May 2009 15:46 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
ok, another hint.

A day has 24 hours, so 1/3 of a day is 8 hours.

Report message to a moderator

Re: Convert string to time of day [message #402334 is a reply to message #402332] Fri, 08 May 2009 15:48 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member
Thanks but insults are not necessary, its friday ok.

Report message to a moderator

Re: Convert string to time of day [message #402335 is a reply to message #402333] Fri, 08 May 2009 15:51 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member
I knew 1/3 of a day was 8 hours and 3600 seconds in one hour thats how 3600 * 8= 28800. Thanks for the hints but problem is resolved.

Report message to a moderator

Re: Convert string to time of day [message #402336 is a reply to message #402335] Fri, 08 May 2009 16:07 Go to previous messageGo to next message
deahayes3
Messages: 203
Registered: May 2006
Senior Member
For those of you that would like to know the pl/sql version

SELECT TO_CHAR(TRUNC(:n/60/60),'09') ||
 TO_CHAR(TRUNC(MOD(:n,3600)/60),'09') ||
 TO_CHAR(MOD(MOD(:n,3600),60),'09')
FROM dual;

Report message to a moderator

Re: Convert string to time of day [message #402350 is a reply to message #402336] Sat, 09 May 2009 01:27 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
And for those of you who want to use Oracle's date functions like they were meant to be used:
SQL> select to_char(trunc(sysdate) + &n/(24*60*60), 'HH24:MI:SS') from dual;
Enter value for n: 28800
old   1: select to_char(trunc(sysdate) + &n/(24*60*60), 'HH24:MI:SS') from dual
new   1: select to_char(trunc(sysdate) + 28800/(24*60*60), 'HH24:MI:SS') from dual

TO_CHAR(
--------
08:00:00

SQL> select to_char(trunc(sysdate) + &n/(24*60*60), 'HH24:MI:SS') from dual;
Enter value for n: 41040
old   1: select to_char(trunc(sysdate) + &n/(24*60*60), 'HH24:MI:SS') from dual
new   1: select to_char(trunc(sysdate) + 41040/(24*60*60), 'HH24:MI:SS') from dual

TO_CHAR(
--------
11:24:00


Don't try to re-invent the wheel

Report message to a moderator

Re: Convert string to time of day [message #402351 is a reply to message #402350] Sat, 09 May 2009 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Another way:
SQL> select interval '&n' second from dual;
Enter value for n: 28800
INTERVAL'28800'SECOND
-----------------------------------------------------------------
+00 08:00:00.000000

1 row selected.

SQL> /
Enter value for n: 41040
INTERVAL'41040'SECOND
-----------------------------------------------------------------
+00 11:24:00.000000

1 row selected.

Now you can SUBSTR what you want to keep.

Regards
Michel

Report message to a moderator

Re: Convert string to time of day [message #402352 is a reply to message #402351] Sat, 09 May 2009 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Or rather:
SQL> select numtodsinterval('&n','second') from dual;
Enter value for n: 28800
NUMTODSINTERVAL('28800','SECOND')
---------------------------------------------------------------------------
+000000000 08:00:00.000000000

1 row selected.

SQL> /
Enter value for n: 41040
NUMTODSINTERVAL('41040','SECOND')
---------------------------------------------------------------------------
+000000000 11:24:00.000000000

1 row selected.

Regards
Michel

Report message to a moderator

Re: Convert string to time of day [message #402357 is a reply to message #402336] Sat, 09 May 2009 04:11 Go to previous message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
deahayes3 wrote on Fri, 08 May 2009 22:07
For those of you that would like to know the pl/sql version

SELECT TO_CHAR(TRUNC(:n/60/60),'09') ||
 TO_CHAR(TRUNC(MOD(:n,3600)/60),'09') ||
 TO_CHAR(MOD(MOD(:n,3600),60),'09')
FROM dual;


No PL/SQL there. Just SQL. (Or is it that it's Friday and their definitions change?)

Report message to a moderator

Previous Topic: Trigger Error
Next Topic: Global temporary table
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Aug 07 05:54:29 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.