CODEWITHSUNDEEP
sql-serverdatetimesql-server-2012
Nate Pet
Nate Pet

Reputation: 46282

How do I compare a SQL Server datetime using just the date, not the time?

I like to compare on 2 dates as following when I am doing a join:

    a.requestdate = b.alertDate

but I do not want to take the time into consideration. Just the date.

Wondering what the best way to do this would be. I am using SQL Server 2012 (t-sql)

Upvotes: 0

Views: 61

Answers (4)

Joachim Isaksson
Joachim Isaksson

Reputation: 181027

The most efficient if you're going to join on it is probably to create indexed computed columns with the dates, and join on them instead. Something like;

CREATE TABLE test (
  id INT,
  request_datetime DATETIME,
  request_date AS CAST(request_datetime AS DATE)
);

CREATE INDEX d_ix ON test(request_date);

This will allow the join to use exact matching on the index, and things should run faster.

Upvotes: 0

user240141
user240141

Reputation:

Simple, if using Sql 2008 or above

Convert(date,requestdate) = Convert(date,alertdate)

Upvotes: 0

PCG
PCG

Reputation: 1207

Or you can cast the fields to Dates.....but I prefer @ravindra's solution. 

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

Upvotes: 1

Ravindra Gullapalli
Ravindra Gullapalli

Reputation: 9178

In where condition, you can use datediff function like this.

datediff(day, a.requestdate, b.alertdate) = 0

Upvotes: 0

Related Questions