15,747,059 members
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
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

## Solution 5

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
- 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

## Solution 1

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

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

23 - 22 = 1

v2
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

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