Operand data type datetime2 is invalid for subtract operator.

by

Recently, I was working on a requirement to calculate time differences in hours for one of my clients, and after executing a select query, I encountered the error ‘Operand data type datetime2 is invalid for the subtract operator.’ In this article, I will walk you through the cause of this error and the respective solution.

Operand data type datetime2 is invalid for subtract operator.

I was trying to execute the below SQL query.

SELECT EndTime - StartTime 
FROM RunDetails
WHERE ID = 1;

After executing the above query, I got this error as shown in the screenshot below.

Operand data type datetime2 is invalid for subtract operator.

What Causes This Error?

The error occurs because SQL Server doesn’t allow direct subtraction of datetime2 data types using the minus (-) operator.

Solution -1: Using the DATEDIFF Function

The most common and recommended method for calculating the difference between two datetime2 values is to use the DATEDIFF function.

Syntax:

DATEDIFF(datepart, startdate, enddate)

Where:

  • datepart: The unit you want the difference in (second, minute, hour, day, month, year, etc.)
  • startdate: The earlier datetime value
  • enddate: The later datetime value

We can use the below query to calculate the hour differences.

SELECT DATEDIFF(hour, StartTime, EndTime) AS HoursDifference
FROM RunDetails
WHERE ID = 1;

After executing the above query, I got the expected output without any error as shown in the screenshot below.

Operand data type datetime2 is invalid for subtract operator

Solution – 2: Using DATEDIFF_BIG for Larger Intervals

If you’re working with very large time differences that might exceed the range of an integer (2,147,483,647), use DATEDIFF_BIG instead:

SELECT DATEDIFF_BIG(second, StartTime, EndTime) AS SecondsDifference
FROM RunDetails
WHERE ID = 1;

After executing the above query, I got the expected output without any error as shown in the screenshot below.

operand data type datetime2 is invalid

You may also like the following articles.