Click here to Skip to main content
15,309,768 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi friends,
I need to calculate total no. Of months and days between two dates. I have problem in this query. Please help me.
In this query 22/07/2021 and 23/06/2021
Ans shows 1 day. But actual answer is 29 days.

What I have tried:

select@days=datepart(d,@currentdatetime)-datepart(d,@dateofbirth)
Posted
Updated 30-Jul-21 5:43am
Comments
CHill60 26-Jul-21 8:03am
   
You have several solutions here but no-one has really explained what you did wrong. pkfox is the closest by explaining you are just subtracting 22 from 23.
I.e. You have not taken into account that the dates are in different months.
But you don't have to worry about that if you use the correct function. So instead of using the subtraction operator and datepart use DATEDIFF (Transact-SQL) - SQL Server | Microsoft Docs[^] as shown in Solution 3

-- this gives you 23
select@days=datepart(d,@currentdatetime)

-- this gives you 22
datepart(d,@dateofbirth) 


23 - 22 = 1
   
v2
Comments
Member 10611310 24-Jul-21 6:54am
   
I need difference between 2 dates.. pls tell me.
Member 10611310 24-Jul-21 7:20am
   
Please anyone give correct solution for this. Am trying 2 days for this concept. I need months and days between 2 dates.
   
Solution 4 will not work if the period includes a leap year (because they have assumed 365 days in a year, or an average month length in days of 30.

E.g. try that code with a @ToDate of '2024-08-19' and it will return 3 years 1 Months and 28 Days
Try this approach instead
- Calculate the number of months between the two dates and divide by 12 to get the difference as full years
- Add that number of years to the start date to give an interim date to make life easier
- Calculate the number of DAYS between the new interim date and the To Date. Divide that number of days by 30.5 to get the number of full months between the dates.
- Add that number of months to the interim date
- Calculate the number of days between the (new) interim date and the To Date
SQL
declare @years int = (SELECT CAST(DATEDIFF(MONTH, @fromdate, @todate) / 12 as int));
declare @interimDate date = (SELECT DATEADD(YEAR, @years, @fromdate));
declare @months int = (SELECT CAST(DATEDIFF(DAY, @interimDate, @todate) / 30.5 as int));
set @interimDate = (SELECT DATEADD(MONTH, @months, @interimDate));
declare @days int = (SELECT DATEDIFF(DAY, @interimDate, @todate));
For this test data
SQL
DECLARE @FromDate DATE  = '2021-06-23';
DECLARE @ToDate DATE = '2023-08-19';
you will get 2 Years 1 Months 27 Days and for this test data
SQL
DECLARE @FromDate DATE  = '2021-06-23';
DECLARE @ToDate DATE = '2024-08-19';
you will get 3 Years 1 Months 27 Days
   
SQL
 --Please use the following script, might be the following script will give you your final solution,

DECLARE @FromDate DATE  =CONVERT(DATE,'23/06/2021',103) -- DD/MM/YYYY
DECLARE @ToDate DATE = CONVERT(DATE,'19/08/2023',103) -- DD/MM/YYYY

SELECT DATEDIFF(DAY,@FromDate,@ToDate) [TotalDays]

--787 days

--Query to find the number of Years, Months, Days...

SELECT DATEDIFF(YEAR,@FromDate,@ToDate) as [Years],
(DATEDIFF(DAY,@FromDate,@ToDate) -(DATEDIFF(YEAR,@FromDate,@ToDate))*365)/30 as [Months],
DATEDIFF(DAY,@FromDate,@ToDate) - (
(DATEDIFF(YEAR,@FromDate,@ToDate)*365) +
(((DATEDIFF(DAY,@FromDate,@ToDate) -(DATEDIFF(YEAR,@FromDate,@ToDate))*365)/30)*30)) as [Days]

--OutPut - Years  Months	Days
--         2	   1	    27

--Thanks..!!!
   
v2
Comments
CHill60 30-Jul-21 11:44am
   
Reason for my downvote - this produces inaccurate results for Leap Years because you have used 30 as the average month length. See my solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900