Question

Raghavendra Narayana on Tue, 19 Jun 2018 07:14:43


I am facing issue if I am subtracting dates from datetime and datetime2 datatypes, many columns are using DATETIME2 and doing lots calculations are using GETDATE(), but subtract kind of operation is not allowed for such cases.

QUESTION: What is recommended in AZURE SQL DWH, Datetime or Datetime2?  

Is there any workaround or better way of handling for below statements?

--- SQLs and ERRORS ---

DECLARE @D2 DATETIME2;
SELECT GETDATE() - @D2;
ERROR: The data types datetime and datetime2 are incompatible in the subtract operator.

DECLARE @D1 DATETIME;
DECLARE @D2 DATETIME2;
SELECT @D1 - @D2;

ERROR: The data types datetime and datetime2 are incompatible in the subtract operator.

---


Raghavendra Narayana


Replies

Mike Ubezzi (Azure) on Thu, 13 Sep 2018 16:03:35


Hi Raghavendra,

Did you get this figured out? I provided some links. DateTime2 is preferred but it appears there is possibly a usage issue here, and not necessarily a DateTime versus DateTime2 issues. 

CAST - The data types datetime and datetime2 are incompatible in the add operator

DateTime2 vs DateTime in SQL Server

datetime (Transact-SQL)

datetime2 (Transact-SQL)

Regards,

Mike