Operand data type date is invalid for subtract operator.

by

Recently, I was working on a requirement to calculate the days difference using the subtraction operator along with a SQL script, and after executing that script, I got the error Operand data type date is invalid for subtract operator.

Operand data type date is invalid for subtract operator.

I was executing the query below for calculating the day difference.

SELECT 
    student_id,
    full_name,
    admission_date,
    CAST(CAST(GETDATE() as date) - admission_date as int) AS employment_days
FROM nstudents
WHERE location IN ('New York, NY', 'Chicago, IL', 'Los Angeles', 'Houston');

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

Operand data type date is invalid for subtract operator.

Cause of this error

This error occurs in SQL Server because we are attempting to perform arithmetic operations directly on DATE data types, which is not permitted.

Solution

To solve this issue, we first need to convert the date datatype to datetime using the following query.

SELECT 
    student_id,
    full_name,
    admission_date,
    CAST(GETDATE() - CAST(admission_date AS DATETIME) AS INT) AS employment_days
FROM nstudents
WHERE location IN ('New York, NY', 'Chicago, IL', 'Austin', 'Houston');

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

Operand data type date is invalid for subtract operator

You may also like the following articles.