SQL Between Function Isn't Getting All Data It Should Get

Question

There is a standard query to pull information from a table.

I am using the between function to pull data from a specific date range. But there is a problem. Let's say the date range is 2023-6-1 and 2023-6-30

I should be able to pull all data that has that date range but instead I am missing the dates from 2023-6-5 to 2023-6-10 and 2023-6-25 to 2023-6-27

But that data is there originally in the table and I am not sure why I cannot pull that date range.

Just to note that the date column is a string not in datetime format.

Is there something I am doing wrong with the query?

Select *
FROM table1
WHERE docDate between '2023-6-1' to '2023-6-30'

Answer 1

It seems like you've stumbled upon an issue with date comparison when the dates are stored as strings which it is not very recommended as a best practice.

The issue with your query is the use of the 'between' clause with string dates. Since the dates are strings, the database is performing a lexicographic (alphabetical) comparison, not a date comparison.

CREATE TABLE table1 (
    id INT PRIMARY KEY,
    docDate VARCHAR(10)
);

INSERT INTO table1 (id, docDate) VALUES
    (1, '2023-06-01'),
    (2, '2023-07-04'),
    (3, '2023-06-05'),
    (4, '2023-06-10'),
    (5, '2023-06-11'),
    (6, '2023-08-15'),
    (7, '2023-06-20'),
    (8, '2023-10-25'),
    (9, '2023-06-27'),
    (10, '2023-06-30');

10 rows affected

SELECT *
FROM table1
WHERE CAST(docDate AS DATE) BETWEEN '2023-06-01' AND '2023-06-30'

id	docDate
1	2023-06-01
3	2023-06-05
4	2023-06-10
5	2023-06-11
7	2023-06-20
9	2023-06-27
10	2023-06-30

fiddle

Make sure that the date strings are in the proper format (YYYY-MM-DD) for this conversion to work.