Difference between 2 dates in sql.

Ankit Shukla

6y1.2kSQL

Hello,

Suppose I have 2 dates "21/10/2019" and "24/11/2020". How to get time period in dd/MM/yyyy formate?

Thanks in advance.

11 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kamil Krupski6y ago
Accepted answer
this should works:
DECLARE @Date1 date = '2020-04-15'
DECLARE @Date2 date = '2016-06-19'

SELECT
    Y + ' years ' + M + ' months ' + D + ' days' [TotalExp]
FROM (
    SELECT DATEADD(D,DATEDIFF(D, @Date2, @Date1),'18991231')[Date]
)Base
CROSS APPLY(
    SELECT
        CAST(YEAR([Date]) % 1900 as varchar(10)) [Y]
        , CAST(MONTH([Date]) - 1 as varchar(10)) [M]
        , CAST(DAY([Date]) - 1 as varchar(10)) [D]
)DateParts
+2
geetha velusamy4y ago
Diffrence between two dates:
select
    startdate
, enddate
, DATEDIFF(YY,startdate , enddate)
from
date
select
    startdate
, enddate
, DATEDIFF(MM,startdate , enddate)
from
date
select
    startdate
, enddate
, DATEDIFF(DD,startdate , enddate)
from
date

0
satheesh d4y ago
Let us consider start date and end date from the table.

The DATEDIFF() helps to find the exact number of days, months and year between two different dates.
SELECT CAST(DATEDIFF(yy, t.StartDate, t.EndDate) AS varchar(4)) +' year '+
CAST(DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.StartDate, t.EndDate), t.StartDate), t.EndDate) AS varchar(2)) +' month '+
CAST(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, DATEDIFF(yy, t.StartDate, t.EndDate), t.StartDate), t.EndDate), DATEADD(yy, DATEDIFF(yy, t.StartDate, t.EndDate), t.StartDate)), t.EndDate) AS varchar(2)) +' day' AS result
FROM table_3 t
Final Output will be
0
Ankit Shukla6y ago
Thank you all.
0
Rajeesh Menoth6y ago
Hi,

Try the following example :

https://stackoverflow.com/questions/6054794/how-to-change-default-systemdate-from-ymd-to-dmy
0
Jignesh Kumar6y ago
Hi ankit,

Please try this link,
https://www.c-sharpcorner.com/article/get-difference-between-two-dates-in-C-Sharp/
0
Amit Gupta6y ago
You can find your solution from the below link

https://stackoverflow.com/questions/1541570/how-to-use-datediff-to-return-year-month-and-day
0
Vikas Jha6y ago
Dear Ankit,

I have this code to get difference in hh:mm:ss. Can you try to modify as per your requirment.
Please find below code
declare @StartDate datetime, @EndDate datetime
select @StartDate = '10/01/2020',@EndDate='10/04/2020'
select convert(varchar(5),DateDiff(s, @startDate, @EndDate)/3600)+':'+convert(varchar(5),
DateDiff(s, @startDate, @EndDate)%3600/60)+':'+convert(varchar(5),(DateDiff(s, @startDate, @EndDate)%60)) as [hh:mm:ss]
Please let me know if you require more help.
0
Ankit Shukla6y ago
Dear Sir,

This is not that soluation, which I needs..

Ex: If an employee joined a company on "2016-06-19" and today date is "2020-04-15". So I need total experience of that employee.
2020-04-15 - 2016-06-19 = 3 years 9 months 26 days

Thanks in advance.
+2
Vikas Jha6y ago
Hey Ankit,

If you want existing date in "dd/mm/yyyy" . You can try below code
SELECT CONVERT(varchar, getdate(), 103)
//Response I got 20/06/2020
In place of getDate(), pass ur column value.Here style 103 is dd/mm/yyyy format. If you want to calculate the difference you may need to write some store procedure or function to retrive the date in dd/mm/yyyy format.
0
Amit Gupta6y ago
You have to query in the format of yyyy/MM/dd and if your datatype is some else than date or datetime then change it to date format
0