0

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.

Milos Stojanovic
  • 172
  • 2
  • 11
  • Try this DateTime dateTime1 = DateTime.Parse(dateTimeStr); and get result in whatever format You want like Result=dateTime1. ToString("dd/MM/yyyy"); – Karthik Karnam May 06 '22 at 11:45

3 Answers3

1

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), ".", "")) ;
Crowcoder
  • 11,250
  • 3
  • 36
  • 45
  • I tried. I get this error `Undefined function 'Replace' in expression`. This is my query: `select * from table where ? < DateValue(Replace(Replace(move_date, \".\", \"/\", 1, 2), \".\", \"\"))`. It also throws same error when I change double quotes to single quotes. – Milos Stojanovic May 06 '22 at 12:22
  • It should work with the [Access provider](https://stackoverflow.com/questions/24908724/undefined-function-replace-in-expression-replace-alternative), I'm not sure if changing providers is an option for you. – Crowcoder May 06 '22 at 12:42
  • I solved problem with installing Microsoft Access Database Engine 2010 Redistributable. But comparing dates doesn't really work well. My searchDate is like this: `DateTime searchDate = DateTime.Parse("20.4.2022.")`. I have this date in database `5.4.2022.` but in this query it returns record with that date when it shouldn't. – Milos Stojanovic May 06 '22 at 12:45
  • .NET cannot parse that string to a date as-is. You will have to manipulate that string on the .NET side too, to make it parseable. – Crowcoder May 06 '22 at 12:53
  • It might be problem with my Visual Studio. It suddenly started throwing internal errors connected with debugging mode. – Milos Stojanovic May 06 '22 at 12:54
  • It should have been throwing errors on that DateTime.Parse from the start. I assumed you had some kind of exception handling that defaulted the date to today or something. – Crowcoder May 06 '22 at 12:59
  • It doesn't throw any errors. How do you suggest that I solve this problem. By the way, I was parsing dates in that format and was using `DateTime.Compare()` method and it was working just fine. – Milos Stojanovic May 06 '22 at 13:08
  • Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/244545/discussion-between-crowcoder-and-patrick-jane). – Crowcoder May 06 '22 at 13:18
  • If problem is that OLEDB provider is using the first number as the month, is there a way to swap days part and month part of move_date using some function and that I swap same things in user typed date and then run the query? – Milos Stojanovic May 07 '22 at 15:07
0

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.

Milos Stojanovic
  • 172
  • 2
  • 11
0

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.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • I used that query and there is the same problem I had earlier. My searchDate is like this: `DateTime searchDate = DateTime.Parse("20.4.2022.")`. I have this date in database `5.4.2022.` but in this query it returns record with that date when it shouldn't. When I try to get records that have `searchDate < move_date` I get record with date `5.4.2022.`. My format is `day.month.year`. – Milos Stojanovic May 07 '22 at 20:24