Home » SQL & PL/SQL » SQL & PL/SQL » How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle?
How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685116] Tue, 26 October 2021 18:03 Go to next message
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 #685117 is a reply to message #685116] Wed, 27 October 2021 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Your approach of the issue is wrong, do not TO_CHAR SYSDATE but TO_DATE start_time then you have the result in days.

Re: How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685118 is a reply to message #685116] Wed, 27 October 2021 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68042
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3104
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>
Or TIMESTAMP + EXTRACT:

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>
SY.
Re: How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685123 is a reply to message #685119] Wed, 27 October 2021 08:44 Go to previous messageGo to next message
mmm286
Messages: 13
Registered: July 2014
Junior Member
Solomon Yakobson wrote on Wed, 27 October 2021 04:44
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>
Or TIMESTAMP + EXTRACT:

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>
SY.
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?
Re: How to substract 2 varchar2 dates (YYYYMMDDHH24MMSS) in oracle? [message #685124 is a reply to message #685123] Wed, 27 October 2021 08:51 Go to previous message
Andrey_R
Messages: 380
Registered: January 2012
Location: Israel
Senior Member

Try this

select to_char(sysdate - to_date(start_time, 'YYYYMMDDHH24MISS')),
       case
         when (sysdate - to_date(start_time, 'YYYYMMDDHH24MISS')) * 1440 > 15 then
          'TRUE'
         else
          'FALES'
       END DOES_EXCEED_15_MINUTES
  from TABLE
 where job_name = 'EXAMPLE';

[Updated on: Wed, 27 October 2021 09:09]

Report message to a moderator

Previous Topic: Who call me
Next Topic: transpose rows into columns
Goto Forum:
  


Current Time: Wed Dec 01 03:44:04 CST 2021