Simply try it using a simple function : CREATE FUNCTION TOTAL_WEEKDAYS(date1 DATE, date2 DATE) RETURNS INT RETURN ABS(DATEDIFF(date2, date1)) + 1 – ABS(DATEDIFF(ADDDATE(date2, INTERVAL 1 – DAYOFWEEK(date2) DAY), ADDDATE(date1, INTERVAL 1 – DAYOFWEEK(date1) DAY))) / 7 * 2 – (DAYOFWEEK(IF(date1 < date2, date1, date2)) = 1) – (DAYOFWEEK(IF(date1 > date2, date1, date2)) = 7); Test … Read more
I have written an implementation that properly works with dates exactly one year apart. However, it does not gracefully handle negative timespans, unlike the other algorithm. It also doesn’t use its own date arithmetic, instead relying upon the standard library for that. So without further ado, here is the code: DateTime zeroTime = new DateTime(1, … Read more
Try this: static int CountDays(DayOfWeek day, DateTime start, DateTime end) { TimeSpan ts = end – start; // Total duration int count = (int)Math.Floor(ts.TotalDays / 7); // Number of whole weeks int remainder = (int)(ts.TotalDays % 7); // Number of remaining days int sinceLastDay = (int)(end.DayOfWeek – day); // Number of days since last [day] … Read more
In Oracle, you can simply subtract two dates and get the difference in days. Also note that unlike SQL Server or MySQL, in Oracle you cannot perform a select statement without a from clause. One way around this is to use the builtin dummy table, dual: SELECT TO_DATE(‘2000-01-02’, ‘YYYY-MM-DD’) – TO_DATE(‘2000-01-01’, ‘YYYY-MM-DD’) AS DateDiff FROM … Read more
Take a look at the DateDiff() function. — Syntax — DATEDIFF ( datepart , startdate , enddate ) — Example usage SELECT DATEDIFF(DAY, GETDATE(), GETDATE() + 1) AS DayDiff SELECT DATEDIFF(MINUTE, GETDATE(), GETDATE() + 1) AS MinuteDiff SELECT DATEDIFF(SECOND, GETDATE(), GETDATE() + 1) AS SecondDiff SELECT DATEDIFF(WEEK, GETDATE(), GETDATE() + 1) AS WeekDiff SELECT DATEDIFF(HOUR, … Read more
Month-difference between any given two dates: I’m surprised this hasn’t been mentioned yet: Have a look at the TIMESTAMPDIFF() function in MySQL. What this allows you to do is pass in two TIMESTAMP or DATETIME values (or even DATE as MySQL will auto-convert) as well as the unit of time you want to base your … Read more
I would recommend to use date->diff function, as in example below: $dStart = new DateTime(‘2012-07-26’); $dEnd = new DateTime(‘2012-08-26’); $dDiff = $dStart->diff($dEnd); echo $dDiff->format(‘%r%a’); // use for point out relation: smaller/greater see http://www.php.net/manual/en/datetime.diff.php
if you want to compute differences between two known dates, use total_seconds like this: import datetime as dt a = dt.datetime(2013,12,30,23,59,59) b = dt.datetime(2013,12,31,23,59,59) (b-a).total_seconds() 86400.0 #note that seconds doesn’t give you what you want: (b-a).seconds 0
I would suggest you use the excellent Joda Time library instead of the flawed java.util.Date and friends. You could simply write import java.util.Date; import org.joda.time.DateTime; import org.joda.time.Days; Date past = new Date(110, 5, 20); // June 20th, 2010 Date today = new Date(110, 6, 24); // July 24th int days = Days.daysBetween(new DateTime(past), new DateTime(today)).getDays(); … Read more
When you need something like that on server side, you usually create a table which contains all possible dates between two points in time, and then left join this table with query results. Something like this: create procedure sp1(d1 date, d2 date) declare d datetime; create temporary table foo (d date not null); set d … Read more