Compare Dates in T-Sql, Ignoring the Time Part

Compare dates in T-SQL, ignoring the time part

The most reasonable way to do this is to strip away the time portion of the datetime values and compare the results, and the best way to strip the time portion from a datetime is like this:

cast(current_timestamp as date)    

I used to use and advocate a process that looked like one of the following two lines:

cast(floor(cast(getdate() as float)) as datetime)
dateadd(dd,0, datediff(dd,0, getDate()))

But now that Sql Server has the Date type, which does not hold a time component, there is little reason to use either of those techniques.

One more thing to keep in mind is this will still bog down a query if you need to do it for two datetime values for every row in a where clause or join condition. If possible you want to factor this out somehow so it's pre-computed as much as possible, for example using a view or computed column.

Finally, note the DATEDIFF function compares the number of boundaries crossed. This means the datediff in days between '2009-09-14 11:59:59' and '2009-09-15 00:00:01' is 1, even though only 2 seconds has elapsed, but the DATEDIFF in days between '2009-09-15 00:00:01' and '2009-09-15 11:59:59' is still zero, even though 86,398 seconds elapsed. It doesn't really care at all about the time portion there, only the boundaries. Depending on what your query is trying to do, you might be able to use that to your advantage.

Compare DATETIME and DATE ignoring time portion

Use the CAST to the new DATE data type in SQL Server 2008 to compare just the date portion:

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

SQL: How to compare dates while ignoring the time?

cast both timestamps as dates

For SQL Server

Select * 
from Batch 
where cast(openingtime as date) <> cast(closingtime as date)

For Oracle

Select * 
from Batch 
where trunc(openingtime) <> trunc(closingtime)

How to compare only date part when delivery date is today

You can try a query like below

select * from (tablename)
where CAST(delivery_date as date) = CAST(getdate() as date)

Also if all delivery dates have time part like 00:00:00.000 for sure then

select * from (tablename)
where delivery_date = CAST(getdate() as date) 

would work as good.

Best way to compare dates without time in SQL Server

Don't use convert - that involves strings for no reason. A trick is that a datetime is actually a numeric, and the days is the integer part (time is the decimal fraction); hence the day is the FLOOR of the value: this is then just math, not strings - much faster

declare @when datetime = GETUTCDATE()
select @when -- date + time
declare @day datetime = CAST(FLOOR(CAST(@when as float)) as datetime)
select @day -- date only

In your case, no need to convert back to datetime; and using a range allows the most efficent comparisons (especially if indexed):

declare @when datetime = 'Feb 15 2012  7:00:00:000PM'
declare @min datetime = FLOOR(CAST(@when as float))
declare @max datetime = DATEADD(day, 1, @min)

select * from sampleTable where DateCreated >= @min and DateCreated < @max

Comparing dates in MySQL ignoring time portion of a DateTime field

You could use the DATE function:

SELECT col1, col2, ..., coln
FROM order_table
WHERE date(order_date) = '2012-05-03'

But this is more efficient, if your table is large and you have an index on order date:

SELECT col1, col2, ..., coln
FROM order_table
WHERE order_date >= '2012-05-03'
AND order_date < '2012-05-04'

SQL Server date compare ignores year

Why are you converting to a string? Of course this doesn't work as expected. You're saying:

IF '12/02/2010' > '09/12/2012'

Which of course yields true. You should be saying:

WHERE fldPublishedDate >= '20050401'
  AND fldPublishedDate < DATEADD(DAY, 1, '20050430');

You don't need to convert the column to a date only if you're only comparing date ranges. Most importantly you should be passing in dates in an unambiguous format that is immune from regional and language settings (e.g. YYYYMMDD). 101 isn't a safe style because what month is 04/06/2012 for people in England? That's June, not April. I suspect you should be formatting these dates better in your (classic ASP?) code, or passing in proper datetime data types from whatever language you're using. Finally, don't use NVARCHAR without length:

• Bad habits to kick : declaring VARCHAR without (length)

How can I compare two datetime fields but ignore the year?

You may want to use the built in time functions such as DAY and MONTH. e.g.

SELECT * from table where
MONTH(fieldA) > MONTH(fieldB) OR(
MONTH(fieldA) = MONTH(fieldB) AND DAY(fieldA) >= DAY(fieldB))

Selecting all rows where either the fieldA's month is greater or the months are the same and fieldA's day is greater.

---

Related Topics