Date difference calculation

Question

1.00/5 (1 vote)

See more:

I entered 2 dates. Example 01/01/2012 and 01/01/2013 as mm/dd/yyyy format.
I want to calculate date difference between entered date as (this much of)year (this much of)month (this much of)days. Please help me to solve this.

Posted 4-Nov-12 20:55pm

CSR89

Add a Solution

Comments

n.podbielski 5-Nov-12 2:58am

Soooooooo.... You want this in SQL or Javascript?

CSR89 5-Nov-12 3:28am

SQL

n.podbielski 5-Nov-12 4:14am

You did not wrote which sql you want. If TSQL see answer Aarti.
But I would strongly recommend you to do this in code, not in SQL since it is for data storage not for calculating anything.

MT_ 5-Nov-12 4:58am

Consider start and end date as '2012-01-10' and '2015-05-21'. query in solution 1 will give result as 21 days,3 years, 40 months.

Query in solution 2(mine) below will give result as 3 year, 4 month, 11 days, which is the correct difference between the two dates.

3 solutions

Add a Solution

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

MT_ · Answer 1 · 2012-11-04T22:30:00

I believe, you want difference in year, month and date between two days,

The query will go like

SQL

select (datepart(yyyy,@firstDate) - datepart(yyyy,@secondDate)) as Diffyear
select (datepart(mm,@firstDate) - datepart(mm,@secondDate)) as DiffMonth
select (datepart(dd,@firstDate) - datepart(dd,@secondDate)) as DiffDays

If you want better user experience for the user, you may want to check whether first date is greater than second date before above query, if second date is greater than first date, you should re-arrange the query to avoid showing -1 year or -2 month or -15 days kind of display.

Hope that helps
Milind

Abhimanyu Rawat · Answer 2 · 2012-11-04T22:53:00

Hello

Use this javascript

JavaScript

function CalculateDays1() {
        t1 = document.getElementById('Fromdate').value;
        t2 = document.getElementById('Todate').value;
        if (t2 != '' && t1 != '') {
            //Total time for one day
            var one_day = 1000 * 60 * 60 * 24;
            //Here we need to split the inputed dates to convert them into standard format
            //for furter execution
            var x = t1.split("/");
            var y = t2.split("/");
            //date format(Fullyear,month,date)

            var date1 = new Date(x[2], (x[0] - 1), x[1]);

            var date2 = new Date(y[2], (y[0] - 1), y[1])
            var month1 = x[0] - 1;
            var month2 = y[0] - 1;

            //Calculate difference between the two dates, and convert to days
            _Diff = Math.ceil((date2.getTime() - date1.getTime()) / (one_day)) + 1;

            document.getElementById('TextBoxID-where you want to show calucate day').value = _Diff;
            document.getElementById('HiddenFiledID').value = _Diff;
        }
    }

Now just take one hidden field in .aspx page.

ASP.NET

<asp:hiddenfield id="totDays1" runat="server" xmlns:asp="#unknown" />

Aarti Meswania · Answer 3 · 2012-11-04T21:45:00

Solution 1

see below example

SQL

select datepart(day,'2012-01-10') as days
select datediff(yyyy,'2012-01-10','2012-01-01') as years
select datediff(m,'2012-01-10','2012-01-01')-(datediff(yyyy,'2012-01-10','2012-01-01')*12) as months

pass parameter like this

SQL

select datepart(day,@EndDate) as Days
select datediff(yyyy,@StartDate,@EndDate) as years
select datediff(m,@StartDate,@EndDate)-(datediff(yyyy,@StartDate,@EndDate)*12) as months

Note : Date Format is always yyyy-MM-dd in sql.
Happy Coding!
:)

Posted 4-Nov-12 21:45pm

Aarti Meswania

Updated 4-Nov-12 23:05pm

v5

Comments

CSR89 5-Nov-12 4:03am

The date entered is dynamic, and the period of date may of any value. The answer here specifies only in a fixed period.

Aarti Meswania 5-Nov-12 4:08am

see updated solution

n.podbielski 5-Nov-12 4:14am

Just use declare SQL statement with viariables for those to dates.

CSR89 5-Nov-12 4:31am

But in the case of leap year what will we do?

Aarti Meswania 5-Nov-12 4:33am

yes I was also thinking for that
and you will feel smile with this simple answer as I feel when I got it
:)
just check this updated Final solution, As you wished

MT_ 5-Nov-12 4:57am

consider start and end date as '2012-01-10' and '2015-05-21'. Your query will give result as 21 days,3 years, 40 months. Which is NOT as what OP wants.
Query in my solution below will give result as 3 year, 4 month, 11 days, which is the correct difference between the two dates.

Aarti Meswania 5-Nov-12 5:07am

you are right i forget to edit for months
thanks for suggestion :)
It's updated

Date difference calculation

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Date difference calculation

3 solutions

Solution 2

Solution 3

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us