13

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() )
Engineer M Sajjad
  • 241
  • 1
  • 2
  • 13
  • 2
    DATE is not a function, nor is CURDATE(); `where EnterDate = GETDATE()` – Alex K. Jan 07 '14 at 13:51
  • 2
    If `EnterDate` is a `DATE` datatype, why would it need the `DATE()` function (which doesn't exists) – Lamak Jan 07 '14 at 13:51
  • 1
    Have you looked at [Date and Time Data Types and Functions](http://technet.microsoft.com/en-us/library/ms186724.aspx)? Neither a `DATE` function or `CURDATE()` are listed. – Martin Smith Jan 07 '14 at 13:51

5 Answers5

20

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()

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • 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 ?? – M.Ali Jan 07 '14 at 13:58
  • 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. – D Stanley Jan 07 '14 at 14:04
  • constructive criticisim : please have a look here http://msmvps.com/blogs/robfarley/archive/2010/01/22/sargable-functions-in-sql-server.aspx – M.Ali Jan 07 '14 at 14:07
  • 1
    @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](http://stackoverflow.com/a/10854024/1081897) conversion from `datetime` to `date` is sargable. – D Stanley Jan 07 '14 at 14:18
  • Thank you for the link and the explanation. – M.Ali Jan 07 '14 at 14:20
4

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.

M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • 1
    "I have a field Date of type DATE", means no need to cast the column to a `DATE` – Lamak Jan 07 '14 at 13:53
  • @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 Jan 07 '14 at 13:55
3

More efficient one is 

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

Thanks @D Stanley @marc_S and @Mihai

Engineer M Sajjad
  • 241
  • 1
  • 2
  • 13
0

Finally I get it done by
WHERE EnterDate > Convert(DATETIME,Convert(varchar,DATEADD(DAY,0,GETDATE()),101))

Engineer M Sajjad
  • 241
  • 1
  • 2
  • 13
0

This is not the exact answer but example how to trim the time part from the date time variable

CONVERT(VARCHAR(10),date_manufactured,10) =CONVERT(VARCHAR(10),@startdate,10))

Pradeep Samaranayake
  • 1
  • 1