How to compare only date part when delivery date i

Question

I'm trying to create a report that gets records from a SQL Server database where the delivery date is today.

I've tried

select * from (tablename)
where delivery_date = getdate()

Although that didn't give me any errors, it didn't give me any records either.

I'm assuming it is because all dates are like:

2016-03-15 00:00:00.000

Perhaps, I need to truncate the date to remove the time-stamp and then try?

Answer 1

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.

Answer 2

Try this:

DECLARE @Today DATETIME
SET @Today= CONVERT(date, getdate())

select * from (tablename)
where delivery_date = @Today

Answer 3

If delivery_date is always midnight (00:00:00.000), then compare it like this:

select * from (tablename)
where delivery_date = datediff(d, 0, getdate())

Using datediff like this is a quick way to truncate the time part of a datetime value.

Answer 4

I'd just create 2 params. One for StartTime and one for EndTime and use those in my query.

DECLARE @StartTime DATETIME,
        @EndTime DATETIME 

SET @StartTime = DATEDIFF(d,0,GETDATE())
SET @EndTime = DATEADD(d,1,@StartTime)

SELECT  *
FROM    [tablename]
WHERE   delivery_date >= @StartTime
        AND delivery_date < @EndTime

Answer 5

Yo need to remove the time part of the delivery_date field AND the GETDATE() value.

SELECT *
FROM  (tablename)
WHERE DATEADD(dd, DATEDIFF(dd, 0, delivery_date), 0) = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)