I am bit rusty with MS Access ADO OleDb Queries, so hopefully someone can help. I have a DateTime field in MSAccess which stores both date and time. I would like to return all records that are in provided date range, and I would like to group by different date.
Example:
SELECT DATEVALUE(p.DateField), SUM(c.SomeField)
FROM ParentTable p INNER JOIN ChildTable c ON p.Id = c.ParentId
WHERE DATEVALUE(p.DateField) BETWEEN DATEVALUE(@DateFrom) AND DATEVALUE(@DAteTo)
GROUP BY DATEVALUE(p.DateField)
This query works on most of the languages I have tried, but it does not work when Serbian settings is selected in Region (Latin or Cyrillic) on Win8. The reason for this is because in the Short Date format there is a (.) sign at the end (ex d.M.yyyy.). If I remove that (.) at the end of short date in regional settings (d.M.yyyy), then everything works fine.
Now, can someone share some idea how to solve the problem, so the query works with default serbian regional settings? I guess it is obvious from the query I posted what I am after. Maybe there is a different approach with query to achieve the same?
I am using ADO.NET and OleDbClient, and creating a parameterized query. Parameter type is of OleDbType.Date
