Hi I'm trying to calculate the difference between two columns of datetime2 type.
However SQL server (2012) doesn't seem to like the following:
select cast ('2001-01-05 12:35:15.56786' as datetime2)
- cast ('2001-01-01 23:45:21.12347' as datetime2);
Msg 8117, Level 16, State 1, Line 2
Operand data type datetime2 is invalid for subtract operator.
Now it works if I cast it to a datetime type:
select cast (cast ('2001-01-05 12:35:15.56786' as datetime2) as datetime)
- cast (cast ('2001-01-01 23:45:21.12348' as datetime2) as datetime);
1900-01-04 12:49:54.443
However, I am losing precision when I cast it to datetime (note the 3 decimal precision above). In this case, I actually need all 5 decimal points. Is there a way to get the interval between two datetime2 columns and still maintain 5 decimal points of precision? Thanks.
To calculate the difference between the arrival and the departure in T-SQL, use the DATEDIFF(datepart, startdate, enddate) function. The datepart argument can be microsecond , second , minute , hour , day , week , month , quarter , or year .
MySQL TIMEDIFF() Function The TIMEDIFF() function returns the difference between two time/datetime expressions. Note: time1 and time2 should be in the same format, and the calculation is time1 - time2.
In MS SQL Server, the function DATEDIFF is used to calculate the time interval between two date values and return it as an integer. General syntax for DATEDIFF : DATEDIFF(datepart, start_date, end_date) datepart is the unit of the interval to return.
SQL Server DATEDIFF() Function The DATEDIFF() function returns the difference between two dates.
You can simply use DateDiff
Returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.
select DATEDIFF(MILLISECOND, cast('20010101 23:45:21.12347' as datetime2),
cast('20010105 12:35:15.56786' as datetime2))
Unfortunately, attempting to get the precision you require with this:
select DATEDIFF(MICROSECOND, cast('20010101 23:45:21.12347' as datetime2),
cast('20010105 12:35:15.56786' as datetime2))
results in an overflow error:
The datediff function resulted in an overflow.
The number of dateparts separating two date/time instances is too large.
Try to use datediff with a less precise datepart.
One way to achieve the precision you want would be to iteratively break into the granular time components (days, hours, minutes, seconds, etc.) and subtract this from the values using DateAdd(), e.g.
remainingAtLowerGranularity = DateAdd(granularity, -1 * numFoundInStep, value)
To find difference between two dates you need to use function DATEDIFF
select DATEDIFF(millisecond,'20010105 12:35:15.56786','20010101 23:45:21.12347')
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