SQL Server: cannot find data type date and could not establish compatibility

Question

I have a setup script that uses DATE. I am running SQL Server 2008 R2 and he does not like the date type.

Msg 2715, Level 16, State 7, Line 1 Column, parameter, or variable #3: Cannot find data type date. 

So, I tried to set the database compatibility to 100 using the following:

 ALTER DATABASE znode_multifront SET COMPATIBILITY = 100 

I get the following error:

 Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '100'. 

So, I tried to enter SQLCMD mode and used the following:

 EXEC sp_dbcmptlevel znode_multifront, 100; 

Which caused the following error:

 Msg 15416, Level 16, State 1, Procedure sp_dbcmptlevel, Line 70 Usage: sp_dbcmptlevel [dbname [, compatibilitylevel]] 

I am very new to SQL Server. Any help would be greatly appreciated.

Answer 1

 alter database znode_multifront set compatibility_level = 100 go 

You were close. The setting is COMPATIBILITY_LEVEL , not COMPATIBILITY , as in the original message.

To complete the above query, you will need ALTER permission for the database.