1

Using format from Norway dd.MM.yyyy in my computer I've got syntax error when I try to get data from MS Access database. Query:

query = "SELECT * FROM DateTable WHERE StartDate BETWEEN #18.03.2016# AND #19.03.2016#"

If I change to other formats (my computer settings and in query) then everything is ok.

PD: I have the same problem using INSERT. Thanks

Improve this question
2
  • 2
    That's one of the reasons to use parameterized queries Commented Mar 18, 2016 at 22:33
  • Use SQL Parameters - the provider objects know how to pass a NET DateTime type to the underlying DB correctly (in part because they dont convert to text). That looks like Access notation, so why the MySQL tag? Commented Mar 18, 2016 at 23:01

2 Answers 2

Reset to default
1

I would strongly suggest using ISO date format like YYYY-MM-dd for both INSERT/UPDATE and SELECT operations. This will format be understood correctly both by database and .net. The format of date is just a cosmetic issue about displaying. When you have a correct date object you can show it as you wish.

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

1 Comment

Thanks! It's working with all the formats that i need.
0

Don't know your code but if you insist on creating SQL strings you must format the string expressions for the date values to read like this:

query = "SELECT * FROM DateTable WHERE StartDate BETWEEN #2016/03/18# AND #2016/03/19#"
Improve this answer

4 Comments

I have data in Access Database. Datagrid (WPF) will be filled with data coming from this database but filtered by dates that user can choose using a datepicker. So i will have a DateTime type as data to use a filter.. Each user can have a different format in their computer.
The format has no influence; it is for display only.
Anyways, I tried this format yyyy/MM/dd and it's working for all the optiond that i need. Thanks you!
OK, then please mark as answered. Also, I meant the format the user's machine may use ... the format of the string expressions for the date values in the code does matter, of course.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.