tomatocans
asked on
Minutes(Number Column) to HH24MM
Good Morning
Have a table with a defined column elapsed_time(number).
example 90 minutes
I want to convert it to HH24MM.
example 0130
Is this possible in Oracle 8i.
Thanks
Have a table with a defined column elapsed_time(number).
example 90 minutes
I want to convert it to HH24MM.
example 0130
Is this possible in Oracle 8i.
Thanks
ASKER
what if u have 60,000 records with elapsed time
fields u need to update
fields u need to update
There is also another issue. Oracle date columns always contain a date value, whether they contain the time protion or not. If you succeed in storing values when you have supplied only the time (I am not sure that Oracle will accept that) you may still have problems doing comparisons or arithmetic based on those values if the days are different. I am assuming that Oracle will use the current sysdate for the date portion, so a record created just before midnight one day will have a different date than a records created moments later after midnight.
Have you tested this?
Have you tested this?
Why do you want to convert a field
containing elapsed time to a "HH24:MM"
mask?
In what ways are you using the field
(Comparisons,calculations, showing only)?
containing elapsed time to a "HH24:MM"
mask?
In what ways are you using the field
(Comparisons,calculations,
Hi,
The problem definition is not clear. But from what I have interpreted, u have a column of number datatype which is actually storing the elapsed time. Now, u want to update the values so that it will be in the format hhmi. Ok. Then u can run the following statement:
update tmp
set elapsed_time = floor(elapsed_time/60)||mo d(elapsed_ time,60);
Note that u cannot have the format 0130 bcos the datatype is a number and not a character.
The above statement will update the value elapsed_time = 90 to elapsed_time = 130. However, if the elapsed_time = 1440 the value will be converted to 240. Now, if the elapsed_time is greater than 24 hrs, what format do u want it to be in?? Pl. give examples. The solution is always available.
The problem definition is not clear. But from what I have interpreted, u have a column of number datatype which is actually storing the elapsed time. Now, u want to update the values so that it will be in the format hhmi. Ok. Then u can run the following statement:
update tmp
set elapsed_time = floor(elapsed_time/60)||mo
Note that u cannot have the format 0130 bcos the datatype is a number and not a character.
The above statement will update the value elapsed_time = 90 to elapsed_time = 130. However, if the elapsed_time = 1440 the value will be converted to 240. Now, if the elapsed_time is greater than 24 hrs, what format do u want it to be in?? Pl. give examples. The solution is always available.
Have you considered creating a view using a calculation like the one suggested by nitinpai, instead of altering your table? This has the advantage of not requiring an update, and being able to display all of the records as if they had been updated.
Can you explain the advantage(s) you see in having the data stored as something other than a number?
Can you explain the advantage(s) you see in having the data stored as something other than a number?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks
alter table <table-name> modify (elapsed_time date);
Then insert values like this:
insert into <table-name> values (to_date('0130','HH24MI'))
This will store the time value.
If you want to make comparisions on it also use the same format:
select * from <table-name> where elapsed_time > to_date('0230','HH24MI');