SQL Server 2014 Type DATE is not a defined system type

Question

I am using the below query in the stored procedure

SELECT @ICOUNT = COUNT(*)
FROM MESSAGES
WHERE CAST(CREATEDDATE AS DATE) >= CAST(@STARTDATE AS DATE) 
    AND CAST(CREATEDDATE AS DATE) <= CAST(@ENDDATE AS DATE);

Its working correctly in Sql Server 2008, Where as in Sql Server 2014 I'm facing the below error:

Type DATE is not a defined system type.

Wenn

Run SELECT compatibility_level FROM sys.databases where [name] = <YourDbName> (replace <YourDbName> with the actual name of the database. What's the result? Are you sure you are working with 2014 version and not 2012? – Tremann

The Compatibility level from the above query is 90. – Wenn

What is SELECT @@VERSION? – Assignation

Answer 1

If the compatibility level is 90, it means you are not working with SQL Server 2014, since it does not support this compatibility level (lowest supported is 100).

If you are working with 2012 (which is the highest version supporting compatibility level 90), you should change the compatibility level to 110. For 2008 / 2008 r2 version, the maximum compatibility level is 100.

Change the compatibility level using alter database:

ALTER DATABASE database_name   
SET COMPATIBILITY_LEVEL = 110 -- or 100 for 2008 versions.

Tremann

Glad to help :-) – Tremann