A problem with dates (datetime null) and (datetime not null)

Transact-SQL
1

Hello everyone,

I am relatively inexperienced in T SQL and this problem has me baffled.

Every table in the database that has a date field seems to have (datetime, not null) or (datetime, null) as the data type.

If I run a query such as

SELECT *

FROM Table A

WHERE Order_Date = ‘10/01/18’

This will return the desired result set with order dates of 10th January 2018. The format appears as 2018-01-10 17:00:00.000 which is fine. There is one table where this query is not working and the date field in the table is (datetime, null) So the query

SELECT *

FROM Table BB

Where Due_Date = ‘10/01/18’

Returns no results even though I know we have records with this date. If I run

SELECT TOP 100 *

FROM Table BB

I can see records with a due date 10th January. The format as per above 2018-01-10 17:00:00.000 There is another date column in the same table also with a data type (datetime, nnull) and this works as expected!

Why is this simple query not returning results when it seems to work fine in every other table and date column in this database?

Any help would be much appreciated.

Thanks

2

First, to make sure you are getting the correct date compared, use format YYYYMMDD, which can't be "confused" by SQL like xx/xx/yyyy can be.

Where Due_Date = ‘20180110'

Also, be aware that if it's a datetime, and the time is not zero, you may need to do this:

Where Due_Date >= ‘20180110' And Due_Date < ‘20180111'

1 Like
3

Thank you Scott. I will give that a go tomorrow morning. Thanks for taking the time to help me.

4

When you use the criteria '10/01/18' or 20180110' to find matching values in a datetime column SQL Server is looking for '20180110 00:00:00.000'. To find a row with a date of ''20180110 17:00:00.000' you will need to specify the time portion as well or use a range as with Scott's response.

1 Like