In my previous article i have given the basic idea about how to convert time to seconds. In this article i would like to give you the idea about how to convert in to time format in SQL with multiple examples.There are so many times we have requirement to convert hours or seconds to the time format in the programming scenarios. I would like to explain multiple scenarios to time format. We have so many formats of time and there are so many requirements to time formats.
In this section i would like to give you multiple ways to time with examples. There are so many functions which we can use in to the specified time format. I would like to give multiple scenarios in to time format which is hh:mi:ss
Scenario 1 : Seconds to time format using numtodsinterval and to char function.
SQL> select
2 to_char(
3 trunc(sysdate)
4 + numtodsinterval(55494, 'second')
5 ,'hh24:mi:ss'
6 ) As "Current_Time"
7 from dual;
Current Time
--------
13:24:33
If you can see the query closely the 3 key functions we are using to convert the seconds in to time format.
1.to_char function : This is used to change the time format in to character.
2.trunc : The trunc function is used to truncate the seconds in to hours: Minutes format.
3.numtodsinterval : This is important function which is used to convert the seconds in to hh:mm format.
Scenario 2 : Seconds to time format using simple to_char and to_date function
The second scenario is quite simple where we are using to_char and to_date function for converting the seconds to time format. You can add any of the time format.
SQL> select
2 to_char(
3 to_date(55494,'sssss')
4 ,'hh24:mi:ss') As "Current_Time"
5 from dual;
Current_Time
--------
12:24:54
The above example is used to convert in to the correct time format. Here we are using only 2 functions –
Scenario 3 : Using Arithmetic and logical operations in to time format
The third scenario is straightforward way to convert the seconds in to time format.With using simple to_char and trunc and mod functions together we can convert the seconds in to time format. The following query is useful in to time format.
SQL> select
2 to_char(trunc(46854/3600),'fm9900') || ':' ||
3 to_char(trunc(mod(46854,3600)/60),'fm00') || ':' ||
4 to_char(mod(46854,60),'fm00') "Current_Time"
5 from dual;
Current_Time
-------------
13:24:54
Here we have used to_char and trunc,mod functions together to convert the seconds in to the specified time format.
Introduction Cryptocurrencies took the world by storm, setting up a new financial system and breaking…
In my previous article I have given Top 20 technical support interview questions with its…
In my previous articles I have given 15 most asked desktop support interview questions with…
A business analyst is someone who is versed in processes of data analysis used for…
In my previous article I have already given top questions and answers for Desktop support…
In my previous article I have given unix production support interview questions and answers. In…