1

I've been reading all over the place that DATE type should work starting with SQL Server 2008, for example: stackoverflow.com/a/126984/1155650

I'm using SQL Server 2008 R2 and for some reason the DATE type is not recognized. Am I missing something?

When I execute this:

SELECT CONVERT(DATE, GETDATE())

I get this error message:

Msg 243, Level 16, State 1, Line 1

Type DATE is not a defined system type.

Community
  • 1
  • 1
sergeidave
  • 662
  • 4
  • 11
  • 23
  • 3
    Are you running in a compatibility mode other than 2008 R2 (i.e. compatibility mode 100)? – LittleBobbyTables - Au Revoir Apr 08 '14 at 18:49
  • can you query this and tell us if you see date listed: select * from sys.types WHERE name LIKE '%date%' – Angel_Boy Apr 08 '14 at 19:16
  • @Angel_Boy I ran the query and it only returns two types: smalldatetime and datetime – sergeidave Apr 08 '14 at 19:52
  • @LittleBobbyTables When I run a "select name, compatibility_level from sys.databases" I get all the names of my databases and all of them show a compatibility level of 90. Would this be the problem you think? I'm not familiar with compatibility levels and stuff. – sergeidave Apr 08 '14 at 19:58
  • 1
    @sergeidave - yes, that means that while you are using SQL Server 2008 R2, the database is running as if it were a SQL 2005 database. Whoever is in charge of the database may have had a good reason for this, or it may have been by accident. Before changing it, you should read up on it: http://technet.microsoft.com/en-us/library/bb510680(v=sql.105).aspx – LittleBobbyTables - Au Revoir Apr 08 '14 at 20:01
  • @LittleBobbyTables Got it! Can you post this as an answer? I will go ahead and select it as the answer. Thank you! – sergeidave Apr 08 '14 at 20:03

1 Answers1

0

Type DATE is not a defined system type.

Looks clear, your system do no accept DATE type, perhaps you are running on SQL Server 2005 or earlier, or compatibility mode..

Ryx5
  • 1,366
  • 8
  • 10