| How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685116] |
Tue, 26 October 2021 18:03  |
 |
mmm286
Messages: 13
Registered: July 2014
|
Junior Member |
|
|
Hi
I have these varchar2 : 20211026231735 (column startime type varchar2)
So I would like a query to substract actual sysdate to that date and convert the substraction to DAY HOURS AND SECONDS.
select TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') - start_time from TABLE where job_name='EXAMPLE';
I get 4220.
I need to converT these 4220 por example to X days, HH24 HOUR, MM MINUTES, SS SECONDS.
Any help please? Thanks
[Updated on: Tue, 26 October 2021 18:04] Report message to a moderator |
|
|
|
|
|
| Re: How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685118 is a reply to message #685116] |
Wed, 27 October 2021 00:38  |
 |
Michel Cadot
Messages: 68421
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
And forgot the moderator bit:
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.
With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
In the end, feedback to your topics to know if the problem is solved, help future readers with the solution you ended, thanks people who spent time to help you.
[Updated on: Wed, 27 October 2021 00:38] Report message to a moderator |
|
|
|
| Re: How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685119 is a reply to message #685118] |
Wed, 27 October 2021 04:44 |
Solomon Yakobson
Messages: 3214
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Storing dates as string is never a good idea. Anyway, use TIMESTAMP:
with sample as (select '20211026231735' start_time from dual)
select to_number(
substr(
systimestamp - to_timestamp(start_time,'yyyymmddhh24miss'),
1,
10
)
) || ' ' || substr(
systimestamp - to_timestamp(start_time,'yyyymmddhh24miss'),
12,
8
) duration
from sample
/
DURATION
-----------------------------------------------------------------------------
0 06:18:08.
SQL>
with sample as (select '20211026231735' start_time from dual),
t as (select cast(sysdate as timestamp) - to_timestamp(start_time,'yyyymmddhh24miss') diff from sample)
select extract(
day from diff
) || ' day(s) ' ||
extract(
hour from diff
) || ' hour(s) ' ||
extract(
minute from diff
) || ' minute(s) ' ||
extract(
second from diff
) || ' second(s) ' duration
from t
/
DURATION
--------------------------------------------------------------------------------
0 day(s) 6 hour(s) 26 minute(s) 0 second(s)
SQL>
|
|
|
|
| Re: How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685123 is a reply to message #685119] |
Wed, 27 October 2021 08:44 |
|
mmm286
Messages: 13
Registered: July 2014
|
Junior Member |
|
|
Solomon Yakobson wrote on Wed, 27 October 2021 04:44Storing dates as string is never a good idea. Anyway, use TIMESTAMP:
with sample as (select '20211026231735' start_time from dual)
select to_number(
substr(
systimestamp - to_timestamp(start_time,'yyyymmddhh24miss'),
1,
10
)
) || ' ' || substr(
systimestamp - to_timestamp(start_time,'yyyymmddhh24miss'),
12,
8
) duration
from sample
/
DURATION
-----------------------------------------------------------------------------
0 06:18:08.
SQL>
with sample as (select '20211026231735' start_time from dual),
t as (select cast(sysdate as timestamp) - to_timestamp(start_time,'yyyymmddhh24miss') diff from sample)
select extract(
day from diff
) || ' day(s) ' ||
extract(
hour from diff
) || ' hour(s) ' ||
extract(
minute from diff
) || ' minute(s) ' ||
extract(
second from diff
) || ' second(s) ' duration
from t
/
DURATION
--------------------------------------------------------------------------------
0 day(s) 6 hour(s) 26 minute(s) 0 second(s)
SQL>
Thanks!
Do you know how could I substract like you did (sysdate to start-time) and check if the result exceeds 15 minutes in the same query?
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
