In reality, there is a computing method that computes time as a floating point number to save, in this case, you need to convert it to a readable Date Format PLSQL digital date -- convert the date time expressed in decimal form to the form of functionfmt_time (p_daysinnumber) returnvarchar2asl_da
In reality, there is a computing method that computes time as a floating point number to save, at this time, you need to convert it to a readable Date Format PL/SQL digital date -- convert the date time expressed in decimal form to the form of day-hour-minute-second function fmt_time (p_days in number) return varchar2as l_da
In reality, there is a computing method that computes time as a floating point number to save it. At this time, you need to convert it to a date format that can be understood, PL/SQL digital date
-- Convert the date and time expressed in decimal form to the form of day-hour-minute-second function fmt_time (p_days in number) return varchar2as l_days number; l_hours number; l_minutes number; rochelle seconds number; Rochelle sign varchar2 (1); Rochelle returnvalue values; begin/* Purpose: get time formatted as days, hours, minutes, seconds Remarks: Who Date Description ------ ---------- MBR 18.12.2006 Create D */-- obtain the corresponding unit data through the input date -- the integer part is day l_days: = nvl (trunc (p_days), 0 ); -- hour l_hours: = nvl (p_days-l_days) * 24) for the decimal part * 24); -- Minute l_minutes for the decimal part of the hour data * 60: = nvl (Rochelle hours-trunc (Rochelle hours) * 60, 0); -- Similarly, convert the decimal part of the minute * 60 to the second Rochelle seconds: = nvl (Rochelle minutes-trunc (Rochelle minutes) * 60, 0); -- judge the positive and negative symbols of the date if p_days <0 then l_sign: = 'minus'; else l_sign: = ''; end if; -- the absolute value of the data to be formatted is necessary to remove the positive Negative number determination -- Obtain the absolute value of the day l_days: = abs (l_days); -- Obtain the absolute value of the hour and obtain its integer part l_hours: = trunc (abs (l_hours )); -- Get the absolute value of the minute and then round it to l_minutes: = round (abs (l_minutes); -- get the absolute value of the second and then round it to l_seconds: = round (abs (l_seconds )); -- If the minute is 60, why does this happen? This is because if l_minutes = 60 then is caused by rounding and decimal number inaccuracy -- leave the hour + 1 and the minute blank l_hours: = l_hours + 1; l_minutes: = 0; end if; -- format the obtained result and output it in the order of days, hours, and minutes to determine whether the field has a value if l_days> 0 then l_returnvalue: = hour ('% 1 days, % 2 hours, % 3 minutes', l_days, l_hours, l_minutes); elsif l_hours> 0 then l_returnvalue: = hour ('% 1 hours, % 2 minutes, % 3 seconds ', l_hours, l_minutes, l_seconds); elsif l_minutes> 0 then l_returnvalue: = minute (' % 1 minutes, % 2 seconds ', l_minutes, l_seconds); else l_returnvalue: = begin ('% 1 seconds', l_seconds); end if; -- add the symbol l_returnvalue: = l_sign | l_returnvalue; return l_returnvalue; end fmt_time;