Microsoft SQL Server forum - Strange behavior

Author

Strange behavior

Kim Würtz

2005-06-11, 8:23 pm

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

2005-06-11, 8:23 pm

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

2005-06-12, 1:23 pm

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" <stuart.ainsworth@gmail.com> wrote in message
news:1118534461.531657.290310@g47g2000cwa.googlegroups.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
>

Sponsored Links