CODEWITHSUNDEEP
c#datecomparems-access-2007oledb
Milos Stojanovic
Milos Stojanovic

Reputation: 557

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

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.

Upvotes: 0

Views: 973

Answers (3)

Gustav
Gustav

Reputation: 55806

In the query, convert those awful text dates to true Date values:

SELECT 
    *, 
    DateSerial(Mid(move_date, 7, 4), Mid(move_date, 4, 2), Mid(move_date, 1, 2)) As true_move_date 
FROM 
    table

Now, in your code, call this a query with a parameter for the search date and expand the query with your criteria. This parameter should be passed a normal DateTime value.

Upvotes: 0

Milos Stojanovic
Milos Stojanovic

Reputation: 557

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.

Upvotes: 0

Crowcoder
Crowcoder

Reputation: 11514

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), ".", "")) ;

Upvotes: 1

Related Questions