Show Datediff as Seconds, Milliseconds

Show datediff as seconds, milliseconds

SELECT 
  DATEDIFF(MILLISECOND, begin, end) / 1000, 
  DATEDIFF(MILLISECOND, begin, end) % 1000
FROM ...;

If you absolutely must form it as a string in your SQL query (can't your presentation tier do that?), then:

SELECT 
  CONVERT(VARCHAR(12),  DATEDIFF(MILLISECOND, begin, end) / 1000)
  + ',' 
  + RIGHT('000' + CONVERT(VARCHAR(4), DATEDIFF(MILLISECOND, begin, end) % 1000), 3)
FROM ...;

Also I really hope you have better column names than begin and end.

DATEDIFF() seconds with millisecond to float or decimal

This is what I needed. CONVERT(float, DATEDIFF(ms, StartTime, GETDATE()) / 1000.0)

The code i my question was completely wrong because DATEDIFF(ms, StartTime, GETDATE()) returns total number of milisecond between the two dates and not as i thought only difference in the milliseconds part.

The code im my question would work if I used the DATEPART instead DATEDIFF in both expressions:

DATEPART(ss, GETDATE()) - DATEPART(ss, @StartTime) + '.' 
+  DATEPART(ms, GETDATE())  - DATEPART(ms, @StartTime)

Return DATEDIFF in milliseconds on SQL Server 2008R2

How about using cast() or convert()?

SELECT DATEDIFF(second,{ d '1970-01-01'},dateCompleted) * convert(bigint, 1000) AS x

Get time difference between two dates in seconds

The Code

var startDate = new Date();
// Do your operations
var endDate   = new Date();
var seconds = (endDate.getTime() - startDate.getTime()) / 1000;

Or even simpler (endDate - startDate) / 1000 as pointed out in the comments unless you're using typescript.

---

The explanation

You need to call the getTime() method for the Date objects, and then simply subtract them and divide by 1000 (since it's originally in milliseconds). As an extra, when you're calling the getDate() method, you're in fact getting the day of the month as an integer between 1 and 31 (not zero based) as opposed to the epoch time you'd get from calling the getTime() method, representing the number of milliseconds since January 1st 1970, 00:00

---

Rant

Depending on what your date related operations are, you might want to invest in integrating a library such as day.js or Luxon which make things so much easier for the developer, but that's just a matter of personal preference.

For example in Luxon we would do t1.diff(t2, "seconds") which is beautiful.

---

Useful docs for this answer

• Why 1970?
• Date object
• Date's getTime method
• Date's getDate method
• Need more accuracy than just seconds?

SQL Server: datediff function resulted in an overflow when using MILLISECOND

See https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver15#return-value

For millisecond, the maximum difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds.

If you need millisecond above that level, you'll need to write something custom.

SQL Datediff in seconds with decimal places

The StackOverflow magic seems to have worked, despite spending hours on this problem last week, I re-read my question and have now solved this. I thought I'd update with the answer to help anyone else who has this problem.

The problem here was not that there was a large range, there was a negative range. Which obviously results in a negative overflow. It would have been helpful if the SQL Server error was a little more descriptive but it's not technically wrong.

So in my case, this was returning values:

SELECT * FROM pagelog
WHERE pagelog_created > pagelog_end

Either remove the values, or omit them from the initial result set!

Thanks to Ivan G and Andriy M for your responses too

DATEDIFF on seconds

You can do the count in second directly.

Select DATEDIFF(s, @Start, @End)

here more information on datediff: https://msdn.microsoft.com/en-us/library/ms189794.aspx

calculate time difference in milliseconds excel

It appears your date/time is a text value.

A "real" time value would normally be seen as 16:11:52.052

But by replacing the last : with a ., excel will see it as a real time and ordinary math can be done.

Excel stores date/time as days and fractions of a day.

So a formula that should work:

=ROUND((SUBSTITUTE(J1,":",".",3)-SUBSTITUTE(E1,":",".",3))*86400000,0)

Format the result as General or as Number with no decimal places

Convert Difference between 2 times into Milliseconds?

DateTime dt1 = DateTime.Parse(maskedTextBox1.Text);
DateTime dt2 = DateTime.Parse(maskedTextBox2.Text);
TimeSpan span = dt2 - dt1;
int ms = (int)span.TotalMilliseconds;

---

Related Topics