Menu
Is there any way to take the difference between two datetime in sql server?
For example, my dates are
2010-01-22 15:29:55.0902010-01-22 15:30:09.153So, the result should be 14.063 seconds.
231
To calculate the difference between the timestamps in MySQL, use the TIMESTAMPDIFF(unit, start, end) function. The unit argument can be MICROSECOND , SECOND , MINUTE , HOUR , DAY , WEEK , MONTH , QUARTER , or YEAR . To get the difference in seconds as we have done here, choose SECOND .
This can be easily done using equals to(=), less than(<), and greater than(>) operators. In SQL, the date value has DATE datatype which accepts date in 'yyyy-mm-dd' format. To compare two dates, we will declare two dates and compare them using the IF-ELSE statement.
In MS SQL Server, the DATEDIFF function is used to get the difference between two dates in terms of years, months, days, hours, minutes etc. SELECT DATEDIFF(day, '2018-03-13', GETDATE()) AS "Difference in days"
Calculate difference between two dates of different columns To calculate the difference between two dates in different columns, we use the two columns createdDate and LastLogin of the registration table and apply the DATEDIFF function on these columns.
Just a caveat to add about DateDiff, it counts the number of times you pass the boundary you specify as your units, so is subject to problems if you are looking for a precise timespan. e.g.
select datediff (m, '20100131', '20100201')
gives an answer of 1, because it crossed the boundary from January to February, so even though the span is 2 days, datediff would return a value of 1 - it crossed 1 date boundary.
select datediff(mi, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')
Gives a value of 1, again, it passed the minute boundary once, so even though it is approx 14 seconds, it would be returned as a single minute when using Minutes as the units.
66
SELECT DATEDIFF (MyUnits, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')
Substitute "MyUnits" based on DATEDIFF on MSDN
33
SELECT DATEDIFF(day, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')
Replace day with other units you want to get the difference in, like second, minute etc.
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
