Your calculation is correct for DATE types, but if your values are timestamps, you should probably use EXTRACT (or DATE_PART) to be sure to get only the difference in full days; EXTRACT(DAY FROM MAX(joindate)-MIN(joindate)) AS DateDifference An SQLfiddle to test with. Note the timestamp difference being 1 second less than 2 full days.
SELECT CONVERT(NVARCHAR(10), PaymentDate, 120) [Month], SUM(Amount) [TotalAmount] FROM Payments GROUP BY CONVERT(NVARCHAR(10), PaymentDate, 120) ORDER BY [Month] You could also try: SELECT DATEPART(Year, PaymentDate) Year, DATEPART(Month, PaymentDate) Month, SUM(Amount) [TotalAmount] FROM Payments GROUP BY DATEPART(Year, PaymentDate), DATEPART(Month, PaymentDate) ORDER BY Year, Month
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
By using the Date object and its milliseconds value, differences can be calculated: var a = new Date(); // Current date now. var b = new Date(2010, 0, 1, 0, 0, 0, 0); // Start of 2010. var d = (b-a); // Difference in milliseconds. You can get the number of seconds (as a integer/whole … Read more
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
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
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
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
You can do the following: TimeSpan duration = b – a; There’s plenty of built in methods in the timespan class to do what you need, i.e. duration.TotalSeconds duration.TotalMinutes More info can be found here.
The problem is that because of Daylight Saving Time shift (on Sunday, March 8, 2020), there are 28 days and 23 hours between those dates. TimeUnit.DAYS.convert(…) truncates the result to 28 days. To see the problem (I’m in US Eastern time zone): SimpleDateFormat fmt = new SimpleDateFormat(“dd-MM-yyyy HH:mm:ss”); long diff = fmt.parse(“30-03-2020 00:00:00”).getTime() – fmt.parse(“1-03-2020 … Read more