SQL 2008 setting compatibility level

Question

When I try to add a data-base in SQL Server 2008 (Right clicking on Databases folder->New Database) the only compatibility options given to me in the options tab are 70, 80, and 90.

However, I require the use of the DATE object, which doesn't work unless I set the compatibility level to 100.

I've tried using exec sp_dbcmptlevel mydb, 100; GO

However, I get the following error: Valid values of the database compatibility level are 60, 65, 70, 80, or 90.

What I am missing here?

Answer 1

Leave it blank. Compatibility level is there to allow you to set the compatibility to a lower version of the database (if you need to).

Answer 2

From your description, it certainly appears that the database is a SQL Server 2005 database, not SQL Server 2008. Does it have a "100.something" next to the server name? If not, it's not SQL Server 2008.

Are you using SQL Server 2008 Management Studio, or an earlier version?

Answer 3

Have you by chance tried the following?

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 100