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?

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`). — Ivan Starostin, Mar 15 '16 at 18:07

DhruvJoshi · Accepted Answer · 2016-03-15T18:17:29.957

62

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.

edited Mar 15 '16 at 18:17

answered Mar 15 '16 at 18:07

DhruvJoshi

17,041
6
41
60

1

If all delivery dates have no time portion, then also consider changing the column type to DATE to save some space. – Chris Bergin Mar 15 '16 at 20:02
works perfectly i have a time portion in database date and when i comparing dates i cant get results i want , thanks – Saurabh Solanki Feb 01 '18 at 04:35
Just SQL Server 2008 or later has DATE type. – Vinicius Gonçalves Mar 14 '19 at 18:06

score 5 · Answer 2 · edited Apr 07 '16 at 21:16

5

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.

edited Apr 07 '16 at 21:16

DhruvJoshi

17,041
6
41
60

answered Mar 15 '16 at 18:09

SlimsGhost

2,849
1
10
16

score 2 · Answer 3 · answered Mar 15 '16 at 18:26

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

score 1 · Answer 4 · edited Feb 17 '19 at 12:39

1

Try this:

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

select * from (tablename)
where delivery_date = @Today

edited Feb 17 '19 at 12:39

Prashant Pimpale

10,349
9
44
84

answered Mar 15 '16 at 20:27

Rajashekar V

33
1
7

score 0 · Answer 5 · answered Mar 15 '16 at 18:23

0

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)

answered Mar 15 '16 at 18:23

M. Ruiz

406
4
11

How to compare only date part when delivery date is today

5 Answers5

Linked