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

Question

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.

Try this DateTime dateTime1 = DateTime.Parse(dateTimeStr); and get result in whatever format You want like Result=dateTime1. ToString("dd/MM/yyyy"); — Karthik Karnam
– Karthik Karnam, Commented May 6, 2022 at 11:45

Crowcoder · Accepted Answer · 2022-05-06 11:49:58Z

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

answered May 6, 2022 at 11:49

Crowcoder

11.6k3 gold badges39 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Milos Stojanovic Over a year ago

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.

Crowcoder Over a year ago

It should work with the Access provider, I'm not sure if changing providers is an option for you.

Milos Stojanovic Over a year ago

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.

Crowcoder Over a year ago

.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.

Milos Stojanovic Over a year ago

It might be problem with my Visual Studio. It suddenly started throwing internal errors connected with debugging mode.

|

Milos Stojanovic · Accepted Answer · 2022-05-07 15:48:40Z

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.

answered May 7, 2022 at 15:48

Milos Stojanovic

1,1521 gold badge9 silver badges23 bronze badges

Comments

Gustav · Accepted Answer · 2022-05-07 17:57:21Z

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.

answered May 7, 2022 at 17:57

Gustav

56.4k7 gold badges33 silver badges65 bronze badges

1 Comment

Milos Stojanovic Over a year ago

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.

Collectives™ on Stack Overflow

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

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

9 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related