How to compare only date part when delivery date is today

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?

Sturtevant

You may cast getdate() to date - this will cut off time. Or use date range if delivery_date stored may contain time too (if it is so I'd suggest to use this approach instead of casting both sides to date). – Overflight

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.

Staphylococcus

If all delivery dates have no time portion, then also consider changing the column type to DATE to save some space. – Parris

works perfectly i have a time portion in database date and when i comparing dates i cant get results i want , thanks – Jurado

Just SQL Server 2008 or later has DATE type. – Deaf

Answer 2

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.

Babin

Answer 3

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

Lantha

Answer 4

Try this:

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

select * from (tablename)
where delivery_date = @Today

Doddered

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)

Indian