Strange behavior

[Kim Würtz]

Hi folks

I have an C# app. connecting to a MS-ACCESS database with several tables.

In a specific situations I have problems with a DateTime type in a table.
The problem
is when I want to select records from a table in a specific period the day
and month
seems to be swapped in the query, but it only happens when the swapping
gives a
valid date eg.

12/10/2005 (12. Oct. 2005) returns records on 10/12/2005 (10. Dec. 2005)

23/05/2005 (23. May 2005) returns records correctly since 05/23/2005 is not
a valid date with danish regional settings.

The query is:
"SELECT [ID], [Activity], [BeginDate] FROM TimeReg WHERE [BeginDate] >= #" +
_start + "# " AND [BeginDate] <= #" + _end + "#"

_start and _end are of type DateTime

My PC in running with danish regional settings and if I shift to en-US
settings in the control panel, this
fixes the problem, but that is not a solution for me.

Any suggestions to solve this problem

Thanks in advance.

Kim W.

[Stu]

Hey Kim,

First, the access news groups may be of more assistance than here;
JetSQL in Access is different than TransactSQL in SQL Server, and they
may have some more insight into this.

Obviously, you're running into transation issues because of the
formatting of dates; in the US, 23/05/2005 is not a valid date (since
our date standards default to mm/dd/yyyy). That would explain why
#12/10/2005# would return records in December, because that's the US
standard. As to how to fix it, I don't know.

The first step I would take, however, is to be sure the SQL statement
being passed to your database system is what you think it is. Have you
printed out the SQL statement and pasted it into a query in Access to
see if it gives you the results you want? Are your sure the C#
application is not reformatting your dates?

You could also try tricking the system by forcing the dates on both
sides of the equality to be formatted the same; it's been a long time
since I've worked in access, but I believe there's a FORMAT command
that would do it.

"SELECT [ID], [Activity], [BeginDate] FROM TimeReg WHERE
FORMAT([BeginDate] ,'dd/mm/yyyy')>= FORMAT(#" +
_start + "#,'dd/mm/yyyy') " AND FORMAT([BeginDate],'dd/mm/yyyy') <=
FORMAT(#" + _end + "#,'dd/mm/yyyy')"

Again, the Access gurus in another forum might have better suggestions,
but this is all I could think of.

HTH,
Stu

[Stu]

Hey Kim,

First, the access news groups may be of more assistance than here;
JetSQL in Access is different than TransactSQL in SQL Server, and they
may have some more insight into this.

Obviously, you're running into transation issues because of the
formatting of dates; in the US, 23/05/2005 is not a valid date (since
our date standards default to mm/dd/yyyy). That would explain why
#12/10/2005# would return records in December, because that's the US
standard. As to how to fix it, I don't know.

The first step I would take, however, is to be sure the SQL statement
being passed to your database system is what you think it is. Have you
printed out the SQL statement and pasted it into a query in Access to
see if it gives you the results you want? Are your sure the C#
application is not reformatting your dates?

You could also try tricking the system by forcing the dates on both
sides of the equality to be formatted the same; it's been a long time
since I've worked in access, but I believe there's a FORMAT command
that would do it.

"SELECT [ID], [Activity], [BeginDate] FROM TimeReg WHERE
FORMAT([BeginDate] ,'dd/mm/yyyy')>= FORMAT(#" +
_start + "#,'dd/mm/yyyy') " AND FORMAT([BeginDate],'dd/mm/yyyy') <=
FORMAT(#" + _end + "#,'dd/mm/yyyy')"

Again, the Access gurus in another forum might have better suggestions,
but this is all I could think of.

HTH,
Stu

[Kim Würtz]

Hi

Thanks for the advices, it really saved my day. Using a little
string manipulation and explicity tell the MS-Access the DateTime
format using the FORMAT in the sql string solved the problem.

Best regards

Kim W.

"Stu" <st**************@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hey Kim,

First, the access news groups may be of more assistance than here;
JetSQL in Access is different than TransactSQL in SQL Server, and they
may have some more insight into this.

Obviously, you're running into transation issues because of the
formatting of dates; in the US, 23/05/2005 is not a valid date (since
our date standards default to mm/dd/yyyy). That would explain why
#12/10/2005# would return records in December, because that's the US
standard. As to how to fix it, I don't know.

The first step I would take, however, is to be sure the SQL statement
being passed to your database system is what you think it is. Have you
printed out the SQL statement and pasted it into a query in Access to
see if it gives you the results you want? Are your sure the C#
application is not reformatting your dates?

You could also try tricking the system by forcing the dates on both
sides of the equality to be formatted the same; it's been a long time
since I've worked in access, but I believe there's a FORMAT command
that would do it.

"SELECT [ID], [Activity], [BeginDate] FROM TimeReg WHERE
FORMAT([BeginDate] ,'dd/mm/yyyy')>= FORMAT(#" +
_start + "#,'dd/mm/yyyy') " AND FORMAT([BeginDate],'dd/mm/yyyy') <=
FORMAT(#" + _end + "#,'dd/mm/yyyy')"

Again, the Access gurus in another forum might have better suggestions,
but this is all I could think of.

HTH,
Stu

[Kim Würtz]

Hi

Thanks for the advices, it really saved my day. Using a little
string manipulation and explicity tell the MS-Access the DateTime
format using the FORMAT in the sql string solved the problem.

Best regards

Kim W.

"Stu" <st**************@gmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...

Hey Kim,

First, the access news groups may be of more assistance than here;
JetSQL in Access is different than TransactSQL in SQL Server, and they
may have some more insight into this.

Obviously, you're running into transation issues because of the
formatting of dates; in the US, 23/05/2005 is not a valid date (since
our date standards default to mm/dd/yyyy). That would explain why
#12/10/2005# would return records in December, because that's the US
standard. As to how to fix it, I don't know.

The first step I would take, however, is to be sure the SQL statement
being passed to your database system is what you think it is. Have you
printed out the SQL statement and pasted it into a query in Access to
see if it gives you the results you want? Are your sure the C#
application is not reformatting your dates?

You could also try tricking the system by forcing the dates on both
sides of the equality to be formatted the same; it's been a long time
since I've worked in access, but I believe there's a FORMAT command
that would do it.

"SELECT [ID], [Activity], [BeginDate] FROM TimeReg WHERE
FORMAT([BeginDate] ,'dd/mm/yyyy')>= FORMAT(#" +
_start + "#,'dd/mm/yyyy') " AND FORMAT([BeginDate],'dd/mm/yyyy') <=
FORMAT(#" + _end + "#,'dd/mm/yyyy')"

Again, the Access gurus in another forum might have better suggestions,
but this is all I could think of.

HTH,
Stu