How to calculate month and days between two dates

Question

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 24-Jul-21 0:12am

Member 10611310

Updated 30-Jul-21 5:43am

Add a Solution

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

5 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-07-30T05:43:00

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

pkfox · Answer 2 · 2021-07-24T00:32:00

Solution 1

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

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

23 - 22 = 1

Posted 24-Jul-21 0:32am

pkfox

Updated 24-Jul-21 0:35am

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.

Richard MacCutchan · Answer 3 · 2021-07-24T02:08:00

Solution 2

months and days between two dates sql - Google Search[^]. Took 5 seconds to find.

Posted 24-Jul-21 2:08am

Richard MacCutchan

Er Sagar Mahajan Pune · Answer 4 · 2021-07-30T04:31:00

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

ap.petko · Answer 5 · 2024-02-23T10:13:00

this works for me...

SQL

DECLARE
     @sd DATE = '2024-01-15'
    ,@ed DATE = '2028-02-05'

    ,@y INT
    ,@m INT
    ,@d INT
    ,@d01 DATE
    ,@d02 DATE
;

SELECT @y   = DATEDIFF  (YEAR   , @sd   , @ed   ) ;
SELECT @d01 = DATEADD   (YEAR   , @y    , @sd   ) ;
SELECT @m   = DATEDIFF  (MONTH  , @d01  , @ed   ) ;
SELECT @d02 = DATEADD   (MONTH  , @m    , @d01  ) ;
SELECT @d   = DATEDIFF  (DAY    , @d02  , @ed   ) ;

if (@d < 0) 
BEGIN
    SELECT @m -= 1 ;
    SELECT @d02 = DATEADD(MONTH, @m, @d01) ;
    SELECT @d = DATEDIFF(DAY, @d02, @ed) ;   
END ;

SELECT 
    @sd, @ed, @y, @m, @d
;

How to calculate month and days between two dates

5 solutions

Solution 5

Solution 1

Solution 2

Solution 4

Solution 6

Add your solution here

Preview 0