Difference between 2 dates in sql.

Question

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.

Kamil Krupski · 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

Ankit Shukla · Answer

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.

satheesh d · Answer

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

Amit Gupta · Answer

You can find your solution from the below link https://stackoverflow.com/questions/1541570/how-to-use-datediff-to-return-year-month-and-day

geetha velusamy · Answer

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

Ankit Shukla · Answer

Thank you all.

Rajeesh Menoth · Answer

Hi, Try the following example : https://stackoverflow.com/questions/6054794/how-to-change-default-systemdate-from-ymd-to-dmy

Jignesh Kumar · Answer

Hi ankit, Please try this link, https://www.c-sharpcorner.com/article/get-difference-between-two-dates-in-C-Sharp/

Vikas Jha · Answer

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.

Vikas Jha · Answer

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.

Amit Gupta · Answer

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

Difference between 2 dates in sql.

Answers (11)