OraFAQ Forum: SQL & PL/SQL » Difference between two dates

Home » SQL & PL/SQL » SQL & PL/SQL » Difference between two dates

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference between two dates [message #686976]

Thu, 09 February 2023 06:54

Joy83
Messages: 6
Registered: February 2023

Junior Member

Hi
I need your help to get the difference between two dates

Date1
And Date2

I have three values
Date1 varchar(2)
Date 2 varchar(2)

Date1:='01/01/2023’
Date1:= to date (WorkingDate, 'MM/DD/YYYY');

Date1:='01/01/2020’
Date1:= to date (WorkingDate, 'MM/DD/YYYY');

Report message to a moderator

Re: Difference between two dates [message #686977 is a reply to message #686976]

Thu, 09 February 2023 07:08

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

In general, subtracting one date of date data type from another date of date data type results in the number of days between them.

Report message to a moderator

Re: Difference between two dates [message #686978 is a reply to message #686977]

Thu, 09 February 2023 07:35

Joy83
Messages: 6
Registered: February 2023

Junior Member

I added new myVal type number
I did subtract
I got data type error
Should I convert the dates to date

Report message to a moderator

Re: Difference between two dates [message #686979 is a reply to message #686978]

Thu, 09 February 2023 07:43

Barbara Boehmer
Messages: 9105
Registered: November 2002
Location: California, USA

Senior Member

You need to subtract dates, so both must be date data types, either originally or converted, not character and not number, but your result will be a number (of days). In the example below, hiredate and sysdate are both date data types and subtracting one from the other results in the number of days between them.

Code: [Select all] [Show/ hide]

SCOTT@orcl_12.1.0.2.0> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SCOTT@orcl_12.1.0.2.0> select hiredate, sysdate,
  2  	    sysdate - hiredate as days_between
  3  from   emp
  4  /

HIREDATE        SYSDATE         DAYS_BETWEEN
--------------- --------------- ------------
Wed 17-Dec-1980 Thu 09-Feb-2023   15394.2375
Fri 20-Feb-1981 Thu 09-Feb-2023   15329.2375
Sun 22-Feb-1981 Thu 09-Feb-2023   15327.2375
Thu 02-Apr-1981 Thu 09-Feb-2023   15288.2375
Mon 28-Sep-1981 Thu 09-Feb-2023   15109.2375
Fri 01-May-1981 Thu 09-Feb-2023   15259.2375
Tue 09-Jun-1981 Thu 09-Feb-2023   15220.2375
Thu 09-Dec-1982 Thu 09-Feb-2023   14672.2375
Tue 17-Nov-1981 Thu 09-Feb-2023   15059.2375
Tue 08-Sep-1981 Thu 09-Feb-2023   15129.2375
Wed 12-Jan-1983 Thu 09-Feb-2023   14638.2375
Thu 03-Dec-1981 Thu 09-Feb-2023   15043.2375
Thu 03-Dec-1981 Thu 09-Feb-2023   15043.2375
Sat 23-Jan-1982 Thu 09-Feb-2023   14992.2375

14 rows selected.

Report message to a moderator

Re: Difference between two dates [message #686980 is a reply to message #686979]

Thu, 09 February 2023 08:30

Joy83
Messages: 6
Registered: February 2023

Junior Member

Thanks a lot

Report message to a moderator

Previous Topic:	Convert Date formats
Next Topic:	Cast(Multiset())

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 28 06:56:09 CDT 2025