SQL select where startdate is today's date

Question

I am trying to write a query where the clause is when the start date for an employee is todays date.

select * from tbl_employees
where Startdate = getdate()

The issue is that Startdate is '2014-12-09 00:00:00.000' and the function getdate is coming back with the date and time like '2014-12-09 08:25:16.013'

How can I write a query that only consider's the date?

possible duplicate of [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) — BenR, Dec 09 '14 at 13:55

score 9 · Answer 1 · answered Dec 09 '14 at 13:33

You want just the date portion. The easy way is:

select *
from tbl_employees
where cast(Startdate as date) = cast(getdate() as date);

However, if you want to use an index, it is best not to have the column in a function call. So, this is better:

where (StartDate >= cast(getdate() as date) and StartDate < cast(getdate() + 1 as date))

score 3 · Answer 2 · answered Dec 09 '14 at 13:34

3

select * from tbl_employees 
where CONVERT(VARCHAR(10),Startdate,110) = CONVERT(VARCHAR(10),GETDATE(),110)

answered Dec 09 '14 at 13:34

HaveNoDisplayName

8,291
106
37
47

score 1 · Answer 3 · answered Dec 09 '14 at 13:35

1

You can use to compare only DATE section not time..some thing like

IF CAST(DateField1 AS DATE) = CAST(DateField2 AS DATE)

OR

CONVERT(VARCHAR(10), GETDATE(), 112)

answered Dec 09 '14 at 13:35

score 0 · Answer 4 · answered Dec 09 '14 at 13:37

SELECT CONVERT (date, SYSDATETIME())
    ,CONVERT (date, SYSDATETIMEOFFSET())
    ,CONVERT (date, SYSUTCDATETIME())
    ,CONVERT (date, CURRENT_TIMESTAMP)
    ,CONVERT (date, GETDATE())
    ,CONVERT (date, GETUTCDATE());

/* Returned 
SYSDATETIME()      2007-05-03
SYSDATETIMEOFFSET()2007-05-03
SYSUTCDATETIME()   2007-05-04
CURRENT_TIMESTAMP  2007-05-03
GETDATE()          2007-05-03
GETUTCDATE()       2007-05-04
*/

From: http://msdn.microsoft.com/en-us/library/ms188751.aspx

So using any of these will give you just the date

SQL select where startdate is today's date

4 Answers4