sql - How to query between Date Range

Question

I am querying a table with a time column returned in the format below:

2020-12-30 18:26:23.537
2020-12-30 17:43:19.707
2020-12-30 15:36:13.653
2020-12-30 15:35:23.160
2020-12-30 15:23:33.063
2020-12-30 15:22:42.243
2020-12-30 15:18:26.230
2020-12-30 15:15:20.083
2020-12-30 15:13:08.813

When I query what format this column is it shows as datetime. Update: Using this query it returns date time: SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'vStatusMessagesWithStrings' AND COLUMN_NAME = 'TIme'

I have tried to narrow down results through a where statement in many ways but every time I run it I just get what looks like all the dates returned, all the way back to July.

I have tried just using >= and <= and I have also tried use "Between" but nothing works. I have tried with just the string as 20201228 as 2020-12-28 and as 2020/12/30 but does not help. I read that when using between you need to specify the time as well so I have tried it down to the millisecond but still returns everything. I have tried cast and convert on the date strings and nothing works.

here are a few examples of the different formats I have tried in my where statement:

and (sms.[Time] <= (convert(date,'20201228')) and SMS.[TIME] >= (convert(date,'20201222')))
and (sms.[Time] >= '20201222' and  SMS.[Time] <= '20201228')
and ((sms.[Time] >= Cast('20201222' as DateTime)) and  (SMS.[Time] <= Cast('20201228' as DateTime)))
and (sms.[Time] Between Cast('20201222 00:00:00.000' as DateTime) and  Cast('20201228 00:00:00.000' as DateTime))
and (sms.[Time] Between Cast('2020-12-22 00:00:00.000' as DateTime) and  Cast('2020-12-28 00:00:00.000' as DateTime))
and ((sms.[Time] >= Cast('2020-12-22 00:00:00.000' as DateTime)) and sms.[Time] <= Cast('2020-12-28 00:00:00.000' as DateTime))
and ((sms.[Time] >= '2020-12-22' ) and (sms.[Time] <= '2020-12-28'))

Answer 1

There must be something else going on because every example you say you tried works for me e.g.

declare @Test table ([time] datetime);

insert into @Test ([Time])
values
('2020-12-21T18:26:23.537'),
('2020-12-22T17:43:19.707'),
('2020-12-23T15:36:13.653'),
('2020-12-24T15:35:23.160'),
('2020-12-25T15:23:33.063'),
('2020-12-26T15:22:42.243'),
('2020-12-27T15:18:26.230'),
('2020-12-28T15:15:20.083'),
('2020-12-29T15:13:08.813');

select *
from @Test sms
where sms.[Time] between cast('2020-12-22T00:00:00.000' as datetime) and cast('2020-12-28T00:00:00.000' as datetime);

Returns:

time
2020-12-22 17:43:19.707
2020-12-23 15:36:13.653
2020-12-24 15:35:23.160
2020-12-25 15:23:33.063
2020-12-26 15:22:42.243
2020-12-27 15:18:26.230