Problem with DATE type in SQL Server

Question

I'm trying to CREATE a TABLE with an attribute of type DATE in Microsoft SQL Server 2008. However, when I execute the query,

I get the error:

Column, parameter, or variable #3: Cannot find data type DATE.

I consulted the documentation, and it indicated that DATE is a valid type in SQL Server. It works if I replace "DATE" with "DATETIME". My guess is I'm missing something really simple, but I just started learning SQL and SQL Server; does anybody have an idea?

Answer 1

It is possible that your database is 8.0 or 9.0 compatible. In this case you cannot use DATE type.

Answer 2

If you really want to use DATE, you could change the compatibility level of the database.

ALTER DATABASE dbname SET COMPATIBILITY_LEVEL = 100

That would switch the database to 2008 compatibility, and you should be able to use the DATE datatype.

See http://msdn.microsoft.com/en-us/library/bb510680.aspx