You have two dates (either in your code or in your database columns). How do you calculate the difference between them — in years, months, weeks etc?
This is one of the most common questions on FAQ sites, and the logic in general is quite simple.
You have two dates (either in your code or in your database columns). How do you calculate the difference between them — in years,
months, weeks etc?
This is one of the most common questions on FAQ sites, and the logic in general is quite simple. Most programming languages give you the ability to get “epoch times” (number of seconds/millioseconds from Jan 1, 1970) from a date. Once you have these for both the dates, you get the difference in number of seconds. Then you can simply divide this number by the number of seconds in each day (60*60*24) to get the number of days between the two dates, and so on.
Please note however that as a rule of thumb, if you intend to perform these calculations on values from database columns, you should consider calculating within the database itself as that may be the fastest and most efficient way of doing it. Below are some examples (or pointers) for popular databases and PHP. The PHP logic should be applicable to many programming languages unless they already provide handy in-built functions.
MYSQL:
o Take a look at the following functions:
o PERIOD_DIFF
o DATE_ADD
o DATE_SUB
o From:
http://www.mysql.com/doc/en/Date_and_time_functions.html
POSTGRESQL:
o No functions provided, you can use + and - operators.
o More detailed info about this —
http://techdocs.postgresql.org/techdocs/faqdatesintervals.php
SQL SERVER:
o Code sample:
ldDate1=CTOD(12/01/2002)
ldDate2=CTOD(02/15/2003) ? (YEAR(ldDate2)+MONTH(ldDate2)/;
12-YEAR(ldDate1)-MONTH(ldDate1)/12)*12
ORACLE:
o An interesting article on INTERVALs: http://snipurl.com/171l
o A readymade procedure: http://snipurl.com/171m
MS EXCEL:
o http://support.microsoft.com/?kbid=214134
MSACCESS:
o DATEDIFF
PHP:
<?
$dateDiff = mktime(12,0,0,04,20,2003) - mktime(11,0,0,04,20,2003);
echo ‘Difference in seconds: ’ . $dateDiff . ‘<br />’;
echo ‘<br />Years Difference = ‘. floor($dateDiff/365/60/60/24);
echo ‘<br />Months Difference = ‘. floor($dateDiff/60/60/24/7/4);
echo ‘<br />Weeks Difference = ‘. floor($dateDiff/60/60/24/7);
echo ‘<br />Days Difference = ‘. floor($dateDiff/60/60/24);
echo ‘<br />Hours Difference = ‘. floor($dateDiff/60/60);
echo ‘<br />Minutes Difference = ‘. floor($dateDiff/60);
?>
NOTES
1. The two dates are inside the mktime functions in the first line. Just
for your reference, format of mktime function is as follows:
mktime(HOURS, MINUTES, SECONDS, MONTH, DAY, YEAR)
2. Months difference may not be totally accurate as my code above
approximates 4 weeks in a month (think of February etc). However, it
should suffice for all practical purposes, try it.
3. For all the differences, if you want precise numbers instead of
rounded integers, please remove the "floor()" functions on the
calculations. In general though, rounded numbers are what you are
perhaps looking for.
This post is tagged Tutorials
26 Comments
thank you for the code. and the redesign!
if you want i can share an oracle stored procedure with you which does this extensively.
Ron, thanks. Please feel free to post the code or a URL here.
can i have the the function for geting the current
date in ASP.
Joseph, the “date” command should do the job for you? For instance,
<%= Date() %> HTH
fiuuhhh…
great formula.
i really need it… Thanks….
I Want to check the Date Function in Between the Two date function(i mean given date is already exist or not),and same as to check February month not exceed the date is 28.and also check the leap year or not.
jagadess which language you using?
Thanks for the tutorial. Concise and to the point. I like that.
Hello,
I have one problem with DATE diffence logic. i am using frontend ASP backend SQL Server.
I retrive date field in ASP Ex:06/14/2004 13:15:00 and another one date field Ex: 05/14/2004.
I used this logic but error displayed.
DateDiff(“d”,date1,date2)
how to get two date diffence(no. of days) using ASP.
Thanks in Advance!
Regards,
Ganesh. M
hi just want to know if has anybody have a code snippet where you get display a days and hours remaining from from datediff fuction in sql because i can only get either the day or hour but i dont know how to display both at same time for example Days Remaining 2 days 8 hours if anybody has any answer it will really aprecciated.Thank you
Hello! I just want to thank all of you for the codes it helps me very much….
Hi there im using php to try and calculate whether or not a certain date obtained by a posted value falls between 2 dates that are stored in a mysql database. Any help or advice regarding this would be much appreciated
Best Regards Mat
Hi Matthew, you could do that in two ways:
1. Using PHP: Convert the three dates into a similar time variable (“strtotime” function for instance) and then do simple math.
2. Using MySQL: Compare the dates with the “datediff” function in sql itself by composing the sql dynamically. HTH!
Great stuff, only one problem that i can see, one can easily as do as you suggest and calculate the number of days between two dates, but how (given a language like java) can you then split those days by months to form a schedule, or calendar. e.g. last 7 days in Sepetember, all october and november and first 17 in december. Any advice?
Cheers Dave
Hello, nice and simple code, but there is small mistake. How you could calculate difference between dates in months or years using only div function???
Every hour has 60 min. that’s OK. Every week has 7 days but not every month has 28 (or 30, 31…) days. Also not every year has 365 days. Calculating these differences is far more complex.
It’s nonsense to calculate these differences and using rule every month has 28 days. Etc.
Best Regards Dejf
how can i calculate the difference between 2 dates (enddate and startdate)which both are fields in mysql database,all i want is to return the number of days.
using php
Thank u very much, u made my work very simple(in php getting the days difference between dates).
Had u checked with 2038 and above?
i want to replace the null value in date column while writing to a file with an empty space with out changing the type of column
To find number of days, round() would be better than floor() i think. For dates including a summer or winter hour change (i.e. march). i had this problem on php/windows.
How does one calculate difference of two dates when one of the two dates is null ?
eg. select date1-date2 from dual;
Here if date2 is null, how do we manage to calculate the difference ? Same if date1 contains a null value ?
Hi Adams,
You mention “dual” so I will assume you are working with Oracle.
Let me think of a more graceful way, but you should be able to achieve what you need with case statements in Oracle’s PL/SQL. E.g.,
SELECT
case when date2 is null then date 1
when date1 is null then date2
else date1-date2
end
FROM dual;
Or something like that, hope that gets you started on the right track.
The code is look like easy and useful, but the mktime() function only can support between 2 January 1970 until
18 January 2038, may be this is a limitation for unix date format. So anyone can be solve this problem?
Will someone please help me in writing the Pl/SQL code.. I wanna a code….
I want to calculate the number of days between the two dates provided.
how to get the age from date of birth and todays date in access 2003
plz give answer as soon as possible
Incoming Links