'DATE' is not a recognized built-in function name

Question

I wish to find all records of the current day. I have a field Date of type DATE. I am getting error on sql server

'DATE' is not a recognized built-in function name.

on this line

(DATE(EnterDate) = CURDATE() )

Solution 1

As the error states, there is no DATE function in SQL Server 2008 or 2012 (you tagged both so I'm not sure which you're targeting). You can, however, cast to a date type in SQL Server 2008 and above:

WHERE EnterDate = CONVERT(date,GETDATE())

Note that there's no CURDATE function either, so I've translated that to GETDATE()

Solution 2

Use the following condition in your where cluase

WHERE CAST(DateColumn AS DATE) = CAST(GETDATE() AS DATE)
              ^------------ Your Column Name with `Date` or 'DateTime'  data type

CURDATE() is a mysql function, In Sql-Server we have GETDATE() function to get current date and time.

Solution 3

More efficient one is

WHERE EnterDate > DATEADD(dd, -1, DATEDIFF(dd, 0, GETDATE()))

Thanks @D Stanley @marc_S and @Mihai

Comments

[Lamak]

"I have a field Date of type DATE", means no need to cast the column to a DATE

[M.Ali]

@Lamak I understand that but when a user is trying to use 'CURDATE()' function to get today's date, I find it diffcult to believe that the column he claims to have in DATE data type is really in Date data type :)

[M.Ali]

I know for a fact using CAST() function on a date data type column allows your query to be Sargable is it also true in case of CONVERT() function ??

[D Stanley]

I do not no it for a fact but since CAST and CONVERT work the same for date/datetime types then it should make no difference.

[M.Ali]

constructive criticisim : please have a look here msmvps.com/blogs/robfarley/archive/2010/01/22/…

[D Stanley]

@M.Ali that example shows using CONVERT to go from a datetime to a character type, where CONVERT does work differently (by supporting multiple formats) and apparently is NOT SARGable. Accouring to this answer conversion from datetime to date is sargable.

[M.Ali]

Thank you for the link and the explanation.