Type DATE is not a defined system type

Question

Trying this to cast time away

   select CONVERT(char(10), [Reg Date1], 103) [Reg Date], Regs
    from (
          select cast(SetupDateTime as DATE) [Reg Date1]
               , COUNT(distinct ID) [Regs]
          from dbo.tbl_User
          where cast(SetupDateTime as DATE) 
                between cast((DATEADD (dd , -7 , GETDATE())) AS DATE) 
                    and cast((DATEADD (dd , -1 , GETDATE())) AS DATE)
          group by cast(SetupDateTime as DATE)
        ) a
    order by a.[Reg Date1]

but I get this error

Msg 243, Level 16, State 1, Line 1
Type DATE is not a defined system type.

And why do you even need to cast `SetupDateTime` to something else?? What datatype is `SetupDateTime` in your table?? — marc_s, Dec 14 '11 at 15:38

score 21 · Accepted Answer · edited May 23 '17 at 11:53

21

You are using SQL Server 2005 or earlier. Data type date was introduced in SQL Server 2008. Try to cast to datetime instead.

Have a look at this question on how to remove the time part from a datetime. Best approach to remove time part of datetime in SQL Server

edited May 23 '17 at 11:53

Community

1
1

answered Dec 14 '11 at 15:01

Mikael Eriksson

136,425
22
210
281

I have this problem now thi sis the query – Chris Manko Dec 14 '11 at 15:11

score 0 · Answer 2 · answered Dec 14 '11 at 15:04

0

If you are using Sybase ASE, what you need is datetime.

answered Dec 14 '11 at 15:04

aF.

64,980
43
135
198

Type DATE is not a defined system type

2 Answers2