Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Javascript SQL
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 21:55pm
CSR89508
Comments
n.podbielski at 5-Nov-12 2:58am
   
Soooooooo.... You want this in SQL or Javascript?
Ramdas.C.S at 5-Nov-12 3:28am
   
SQL
n.podbielski at 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.
Milind Thakkar at 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.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

I believe, you want difference in year, month and date between two days,
 
The query will go like
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
  Permalink  
Comments
Ramdas.C.S at 5-Nov-12 5:44am
   
how can i get the answer as 1year2month15days in a single row?
Milind Thakkar at 5-Nov-12 5:48am
   
very simple. instead of three select, write 1
select (datepart(yyyy,@firstDate) - datepart(yyyy,@secondDate)) as Diffyear,
(datepart(mm,@firstDate) - datepart(mm,@secondDate)) as DiffMonth,
(datepart(dd,@firstDate) - datepart(dd,@secondDate)) as DiffDays
 
Hope that helps. If it solved your problem, mark it as solution and/or upvote.
Milind
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hello
 
Use this 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:hiddenfield id="totDays1" runat="server" xmlns:asp="#unknown" />
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

see below example
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
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!
Smile | :)
  Permalink  
v5
Comments
Ramdas.C.S at 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 at 5-Nov-12 4:08am
   
see updated solution
n.podbielski at 5-Nov-12 4:14am
   
Just use declare SQL statement with viariables for those to dates.
Ramdas.C.S at 5-Nov-12 4:31am
   
But in the case of leap year what will we do?
Aarti Meswania at 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
Milind Thakkar at 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 at 5-Nov-12 5:07am
   
you are right i forget to edit for months
thanks for suggestion :)
It's updated

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

  Print Answers RSS
0 OriginalGriff 390
1 Jochen Arndt 165
2 Richard MacCutchan 135
3 DamithSL 95
4 Garth J Lancaster 90
0 OriginalGriff 6,045
1 DamithSL 4,601
2 Maciej Los 4,032
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,220


Advertise | Privacy | Mobile
Web04 | 2.8.141220.1 | Last Updated 5 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100