The data types datetime and date are incompatible in the subtract operator.

by

Recently, I was working on a requirement where I needed to calculate the days between a date and today. After executing a SQL script, I encountered the error: ‘The data types datetime and date are incompatible in the subtract operator.’

The data types datetime and date are incompatible in the subtract operator.

I was executing the below SQL script.

SELECT 
    employee_id,
    first_name,
    last_name,
    hire_date,
    CAST(GETDATE() - hire_date AS INT) AS employment_days
FROM employees
WHERE office_location IN ('New York', 'Chicago', 'Los Angeles', 'Houston');

However, immediately after executing this script, I encountered the error shown in the screenshot below.

The data types datetime and date are incompatible in the subtract operator.

Reason for this error

The error happens because:

  • GETDATE() returns a DATETIME data type
  • hire_date is likely a DATE data type
  • SQL Server doesn’t allow direct arithmetic operations between incompatible data types

Solution

Solution 1: Convert DATE to DATETIME

To fix this error, we need to convert the date to a datetime first using the below query.

SELECT 
    employee_id,
    full_name,
    hire_date,
    CAST(GETDATE() - CAST(hire_date AS DATETIME) AS INT) AS employment_days
FROM nemployees
WHERE office_location IN ('New York, NY', 'Chicago, IL', 'Austin', 'Houston');

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

The data types datetime and date are incompatible in the subtract operator

Solution 2: Use DATEDIFF Function (Recommended)

You can also use the DATEDIFF function to achieve the expected output without any issue. We can execute the query below.

SELECT 
    employee_id,
    full_name,
    hire_date,
    DATEDIFF(DAY, hire_date, GETDATE()) AS employment_days
FROM nemployees
WHERE office_location IN ('New York, NY', 'Chicago, IL', 'Los Angeles', 'Houston');

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

The data types datetime and date are incompatible

You may also like the following articles: