SQL Query Date between not filtering properly

Question

I am querying using my SQL table and trying to form simple date between query.

when i am querying, i am not getting properly correct rows.

My SQL Column is : DOJ (Datatype is datetime) and sample data is below.

DOJ

---

12/25/2023

12/26/2023

12/27/2023

12/28/2023

12/29/2023

12/30/2023

12/31/2023

01/01/2024

01/02/2024

01/03/2024

01/04/2024

the record will show only till 2023 and not showing 2024 records...

below query not working

select doj from tblcustomer where doj between '12/01/2023' and   '01/03/2024'

but, the same query will be working as below

select doj from tblcustomer where doj between '12/01/2023' and   '12/31/2023'

within the year, data will be showing perfect.

if i select, two different year, then record will not be showing anything..

Answer 1

It's not clear what you expect, but in this repro, all rows are return but the last one:

SET DATEFORMAT dmy
CREATE TABLE DOJ (DOJ datetime)
INSERT DOJ (DOJ)
VALUES('12/25/2023'),
('12/26/2023'),
('12/27/2023'),
('12/28/2023'),
('12/29/2023'),
('12/30/2023'),
('12/31/2023'),
('01/01/2024'),
('01/02/2024'),
('01/03/2024'),
('01/04/2024')

select DOJ from DOJ where DOJ between '12/01/2023' and '01/03/2024'

I included the SET DATEFORMAT command, since the outcome will depend on this format.

As other have suggested, double-check the data type.

Answer 2

Hi,

You may use try_cast() as already suggested by Naomi Nosonovsky or you may use

convert(date, doj) between '12/01/2023' and '01/03/2024'.

Regards,

Answer 3

Use the following query to solve your problem. Select doj from tblcustomer where year(doj) in ('2023','2024')