Home » SQL & PL/SQL » SQL & PL/SQL » Difference between two dates
Difference between two dates [message #686976] Thu, 09 February 2023 06:54 Go to next message
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');

Re: Difference between two dates [message #686977 is a reply to message #686976] Thu, 09 February 2023 07:08 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.
Re: Difference between two dates [message #686978 is a reply to message #686977] Thu, 09 February 2023 07:35 Go to previous messageGo to next message
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
Re: Difference between two dates [message #686979 is a reply to message #686978] Thu, 09 February 2023 07:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
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.

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.
Re: Difference between two dates [message #686980 is a reply to message #686979] Thu, 09 February 2023 08:30 Go to previous message
Joy83
Messages: 6
Registered: February 2023
Junior Member
Thanks a lot
Previous Topic: Convert Date formats
Next Topic: Cast(Multiset())
Goto Forum:
  


Current Time: Thu Mar 28 17:23:00 CDT 2024