Menu
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?
802
To compare dates without the time part, don't use the DATEDIFF() or any other function on both sides of the comparison in a WHERE clause. Instead, put CAST() on the parameter and compare using >= and < operators. Let's use StackOverflow database to find all user profiles created on a particular date.
If you want to compare just the date part without considering time, you need to use DateFormat class to format the date into some format and then compare their String value. Alternatively, you can use joda-time which provides a class LocalDate, which represents a Date without time, similar to Java 8's LocalDate class.
A date, time, or timestamp value can be compared with another value of the same data type, a datetime constant of the same data type, or with a string representation of a value of that data type. Additionally, a TIMESTAMP WITHOUT TIME ZONE value can be compared with a TIMESTAMP WITH TIME ZONE value.
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.
194
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.
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
