"John Dann" <n***@prodata.co.uk> wrote in message
news:ocljr01br6s5i4888u93o7b4j3oob5rjs2@4ax.com...
> 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