how to get entries for a specific date?

Question

Good morning all,

We have MSSMS and our tables we have TimeStamp columns which are set as datetime.

How can we select entries based on a specific date apart from using the below?

SELECT *

FROM table

WHERE TimeStamp < '2021-09-03 23:59:59.999' AND TimeStamp > '2021-09-03 00:00:00.000'

Is there a way we can shorten this?

Thank you in advance

Answer 1

You can cast to "date" datatype to truncate the time portion, like

SELECT *
FROM table
WHERE cast(TimeStamp as date) = '2021-09-03' 

But that will prevent the usage of a may existing index.

WHERE TimeStamp < '2021-09-03 23:59:59.999'

That will return a wrong result. Datetime has a precise of 3 milliseconds and SQL Server "round" it up to 2021-09-04, see

https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16

select cast( '2021-09-03 23:59:59.999' as datetime)

Answer 2

SELECT * FROM table

WHERE TimeStamp >= '2021-09-03' AND TimeStamp < '2021-09-04'

Answer 3

Hi @George Botonakis

In addition to the above two methods, you can also try this one.

select * from table where Convert(char(10),TimeStamp,120) = '2021-09-03';

I've done the test.

create table table(TimeStamp datetime);
insert into table values
('2021-09-03 23:59:59'),
('2021-09-03 00:00:00'),
('2021-09-03 00:01:00'),
('2021-09-02 23:59:59'),
('2021-09-04 00:00:00'),
('2021-09-04 02:00:00'),
('2021-09-02 21:45:59');

It will get data in yyyy-mm-dd format.

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.