CODEWITHSUNDEEP
sqlsql-servert-sql
noob
noob

Reputation: 3811

Filter on datetime not working as expected

I have read access to a database which has two fields as below:

Date1                    Place
2019-03-01 15:56:04.000  P1
2019-03-01 16:30:40.000  P2 
2018-06-21 13:30:40.000  P1  And so on

I am running a query as below:

select * from db where Date1='2019-03-01'

But I get below output

Date1  Place

I don't get the two rows.

Expected output

    Date1                    Place
    2019-03-01 15:56:04.000  P1
    2019-03-01 16:30:40.000  P2

Can someone please help.

Upvotes: 0

Views: 2613

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

The problem is the time component on the column.

You can use a range:

select *
from db
where Date1 >= '2019-03-01' and
      Date1 < '2019-03-02'

Or convert the column to a date:

where conert(date, Date1) = '2019-03-01'

Upvotes: 1

Thom A
Thom A

Reputation: 95554

The simple answer is because 2019-03-01 16:30:40.000 and 2019-03-01 aren't the same value. 2019-03-01 is effectively 2019-03-01 00:00:00.

Instead use date boundaries:

SELECT Date1,
       Place
FROM dbo.db --Do you really have a table called db?
WHERE Date1 >= '20190301'
  AND Date1 < '20190302';

I also use the format yyyyMMdd. yyyy-MM-dd can be an ambiguous format; specifically if you are using the older data and time data types (datetime and smalldatetime) where unless you are using the (American) ENGLISH language it's likely that a value of 2019-03-01 will be read in the format yyyy-dd-MM. As such, it's best to use either yyyyMMdd or yyyy-MM-ddThh:mm:ss.nnnnnnn when dealing with literal strings for date (and time) values, as it will always be read correctly, regardless of the language and data type.

Upvotes: 2

Related Questions