Displays dates less than the current day’s date

In my program, I have a data grid view. I make some amounts due for payment today. I made a display of the amounts that are due and have not been paid (late) I want a code that displays the dates less than the current date of the day I tried that following code but it only fetches the lower days and does not look For the month or year if it is greater or not than the current day’s date

        tbl = db.readData("SELECT  * from Payments  where date_batch < CONVERT(varchar(50),GetDate(), 103)", "");
        DgvSearch.DataSource = tbl;

The problem with the previous code is that it doesn’t fetch the date lower by day, month and year. Fetches the date less than the current date in terms of day only I want in terms of day, month and year

Answer

Ok, so I’m going to assume date_batch is a VARCHAR(10) or similar and contains data like:

28/12/2021
29/11/2021
30/08/2021
31/12/2021

As you can see these “strings that look like dates to a human” are in order. They are not in date order, they are in alphabetical order. Big difference – SQLServer sorts strings alphabetically. When you ask for strings “less than x” it uses alphabetical sorting rules to determine “less than”-ness

Don’t stores dates in a string. SQLServer has several date specific datatypes. Use them.

The following process will dig you out of the hole you’ve dug yourself into:

ALTER TABLE Payments ADD COLUMN BatchDate DATE;

UPDATE Payments SET BatchDate = TRY_CONVERT(Date, date_batch, 103);

Now go look at your table and sanity check it:

SELECT * FROM payments WHERE batchdate is null and date_batch is not null

This shows any dates that didn’t convert. Correct their wonky bad data and run the update again.

Do another select, of all the data, and eyeball it; does it look sensible? Do you have any dates that have been put in as 02/03/2021 when they should have been 03/02/2021 etc

Now your table is full of nice dates, get rid of the strings;

ALTER TABLE Payments DROP COLUMN date_batch;

Maybe rename the column, but in SQLServer and c# WeCallThingsNamesLikeThis, we_dont_call_them_names_like_this

sp_rename 'Payments.BatchDate', 'date-batch', 'COLUMN';

Now you can do:

SELECT * FROM payments WHERE batchDate < GetDate()

And never again store dates in a string