J
John Dann
I'm running into a major problem with DateTime formats in an
ado.net-based program, probably as a result of international format
differences. Situation as follows:
The development environment is in Europe/UK (though hopefully the
program will eventually have international uses) and uses Windows
2K/XP with standard (for EU) dd/MM/yyyy hh:mm:ss datetime format and
programming in VB.Net with the v1.1 framework.
Program needs to pull data from an Access database which has one
column populated with .Net datetime values, which is also a primary
index. Viewed from within Access all the datetime values seem
formatted exactly as expected (ie in the format above).
I'm aiming to retrieve data spanning a certain datetime range by using
a query builder procedure which is, in outline:
Public Function QBuilder(startdate as datetime, enddate as datetime)
....
QueryString &= " FROM Table_Name WHERE (DATE_TIME BETWEEN #" _
& startdate.tostring & "# AND #" & enddate.tostring & "#)"
.....
End Function
But though the query string looks OK when it's built (eg in a msgbox),
trying to retrieve data from eg 1/10/2004 to 2/10/2004 gives a set of
rows covering days from January to February.
If I pass startdate and enddate as eg 10/1/2004 to 10/2/2004 then it's
OK, but I run into a different problem. The .Net Date.Parse function
won't accept days >12, which isn't surprising if it expects dates in
dd/MM/yyyy format!
This is really turning into a major headache and I would really
appreciate any pointers as to where to start looking for the problem.
For example maybe I shouldn't use the obvious but maybe incorrect
startdate.tostring
in the query building routine. But what else might be better?
TIA
JGD
ado.net-based program, probably as a result of international format
differences. Situation as follows:
The development environment is in Europe/UK (though hopefully the
program will eventually have international uses) and uses Windows
2K/XP with standard (for EU) dd/MM/yyyy hh:mm:ss datetime format and
programming in VB.Net with the v1.1 framework.
Program needs to pull data from an Access database which has one
column populated with .Net datetime values, which is also a primary
index. Viewed from within Access all the datetime values seem
formatted exactly as expected (ie in the format above).
I'm aiming to retrieve data spanning a certain datetime range by using
a query builder procedure which is, in outline:
Public Function QBuilder(startdate as datetime, enddate as datetime)
....
QueryString &= " FROM Table_Name WHERE (DATE_TIME BETWEEN #" _
& startdate.tostring & "# AND #" & enddate.tostring & "#)"
.....
End Function
But though the query string looks OK when it's built (eg in a msgbox),
trying to retrieve data from eg 1/10/2004 to 2/10/2004 gives a set of
rows covering days from January to February.
If I pass startdate and enddate as eg 10/1/2004 to 10/2/2004 then it's
OK, but I run into a different problem. The .Net Date.Parse function
won't accept days >12, which isn't surprising if it expects dates in
dd/MM/yyyy format!
This is really turning into a major headache and I would really
appreciate any pointers as to where to start looking for the problem.
For example maybe I shouldn't use the obvious but maybe incorrect
startdate.tostring
in the query building routine. But what else might be better?
TIA
JGD