How to compare date in string type in SQL query using MS Access and OLEDB-CodePudding

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.

CodePudding user response：

In Access SQL you can nest Replace and DateValue to convert move_date to something that can be parsed. This does two replacements, first it replaces the first two "."'s with "/" and then does a replacement on that to get rid of the last ".". Finally, it parses as a date you can use with a comparison operator.

On the c# side you should convert the user entered value to a date and then make it a parameter:

SELECT *
FROM table
where @searchDate < DateValue(Replace(Replace(move_date, ".", "/", 1, 2), ".", "")) ;

CodePudding user response：

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.