Best Way to Compare Dates Without Time in SQL Server

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

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)

how to query where date with time = date without time in ms sql

Try like this

SELECT * FROM  Bookings WHERE Convert(VARCHAR(10),StartTime,101) =  Convert(Varchar(10),'2/15/2014',101)

If you are using SQL SERVER 2012

Try this

 SELECT * FROM  Bookings WHERE FORMAT(StartTime,'M/dd/yyyy') = FORMAT('2/15/2014','M/dd/yyyy')

SQL FORMAT

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.

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.

SQL Date Compare by only using Date not Time

In the third from last line in your first query you are comparing two strings.

As such 01/02/2009 is greater than 01/01/2010

I usually do date BETWEEN '01/02/2009 00:00:00.000' AND '01/01/2010 23:59:59.997' but it will be interesting to see a better solution.

How to select date without time in SQL

I guess he wants a string.

select convert(varchar(10), '2011-02-25 21:17:33.933', 120)

120 here tells the convert function that we pass the input date in the following format: yyyy-mm-dd hh:mi:ss.

What is the optimal way to compare dates in Microsoft SQL server?

Converting to a DATE or using an open-ended date range in any case will yield the best performance. FYI, convert to date using an index are the best performers. More testing a different techniques in article: What is the most efficient way to trim time from datetime? Posted by Aaron Bertrand

From that article:

DECLARE @dateVar datetime = '19700204';

-- Quickest when there is an index on t.[DateColumn], 
-- because CONVERT can still use the index.
SELECT t.[DateColumn]
FROM MyTable t
WHERE = CONVERT(DATE, t.[DateColumn]) = CONVERT(DATE, @dateVar);

-- Quicker when there is no index on t.[DateColumn]
DECLARE @dateEnd datetime = DATEADD(DAY, 1, @dateVar);
SELECT t.[DateColumn] 
FROM MyTable t
WHERE t.[DateColumn] >= @dateVar AND 
      t.[DateColumn] < @dateEnd;

Also from that article: using BETWEEN, DATEDIFF or CONVERT(CHAR(8)... are all slower.

---

Related Topics