How to compare only date part when delivery date is today

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.

Try this:

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

select * from (tablename)
where delivery_date = @Today

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

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.