I am using MS Access as database and using OLEDB. In database I am storing dates as strings. I can't change them in date type (I know it would be easier to work with dates as dates and not strings but I can't change it. I also can't change format of date.).
Date format is dd.mm.yyyy. and DD.MM.YYYY.. What I am trying to do is to compare user typed date with some date from database but I am running into problem.
I don't know what functions to use to convert strings do dates and do comparisons. I tried convert, cast, format, cdate etc. Nothing works. Maybe I am not using it right or I just don't know how to do it. I read some topics here on stackoverflow and solutions doesn't work for me bacause they just directed me to documentations and I obviously can't make it work.
So, let's say I have user typed date 23.07.2021. and column in database named move_date that contains string typed dates and that I want to get all records that have move_date before user typed date.
I guess SQL query should look something like this
SELECT * FROM table WHERE func("23.07.2021.") < func(move_date)
where func should be some function that converts string to date in specific format. I just can't find function that works. I suppose I could frame user typed date with #, so it makes it date literal, but still don't know what to do with column.
Any help is appreciated.
I guess this works for me at the moment. I tested with various examples and can't seem to find example where this doesn't work.
SELECT *
FROM table
WHERE DateValue(Replace(Replace(\"{searchDate}\", \'.\', \'/\', 1, 2), \'.\', \'\')) <
DateValue(Replace(Replace(move_date, \'.\', \'/\', 1, 2), \'.\', \'\'))
searchDate is string (it's not parsed to date)
If someone can find examples where this doesn't work, please share.