CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqldatetime
user2296463
user2296463

Reputation: 161

SQL select where startdate is today's date

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?

Upvotes: 6

Views: 25244

Answers (4)

brianforan
brianforan

Reputation: 182

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

Upvotes: 0

user2778823
user2778823

Reputation:

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)

Upvotes: 1

HaveNoDisplayName
HaveNoDisplayName

Reputation: 8497

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

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269663

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

Upvotes: 10

Related Questions