CODEWITHSUNDEEP
sqlsql-server
Mike Swift
Mike Swift

Reputation: 111

Where clause to filter timestamp data by using only date

I am using a where clause to extract data but into database I have datetime stamp and I want to extract data by using only date information.

select *
from invoice
where invoice_date = '2019-06-24'

But I have into database invoice_date = 2019-06-24 04:30:00.000

Upvotes: 4

Views: 12966

Answers (3)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

There are two ways to achieve what you want. Either cast the timestamp to date, which is very readable, or use a time range.

select *
from invoice
where invoice_date >= '2019-06-24' and invoice_date < '2019-06-25';

Working with a time range is slightly less readable, but if you have an index on invoice_date it can be used, so the query may run faster.

Upvotes: 2

Thom A
Thom A

Reputation: 95554

I would personally use "proper" date logic:

SELECT {Column List}
FROM dbo.invoice i
WHERE i.invoice_date >= '2019-06-24'
  AND i.invoice_date < '2019-06-25';

If you're using a parameter, then you would use DATEADD:

SELECT {Column List}
FROM dbo.invoice i
WHERE i.invoice_date >= @DateParam
  AND i.invoice_date < DATEADD(DAY, 1, @DateParam);

Upvotes: 2

Nir Levy
Nir Levy

Reputation: 12953

Cast it to date:

select *
from invoice
where CAST(invoice_date as DATE) = '2019-06-24'

Upvotes: 3

Related Questions