By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,373 Members | 1,792 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,373 IT Pros & Developers. It's quick & easy.

DateTime problem

P: n/a
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
yields 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.


Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Kim Würtz wrote:
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
yields 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.


Date literals in Access must use US format (mm/dd/yyyy) or an unambiguous format
like dd-mmm-yyyy.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com
Nov 13 '05 #2

P: n/a
Kim Würtz wrote in message
<42*********************@dread11.news.tele.dk> :
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
yields 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.


Hi!

Yes, it's by default, when using dynamic SQL - think of it, we're
passing
a string to the Jet engine, where it doesn't know what we are sending
-> it needs an unambiguous format. In VBA, one would format using
for instance something like this:

.... [BeginDate] <= #" + format(_end,"yyyy-mm-dd") + "#"

but how it's done in C#, I don't know. Just note that another format
suggestion showed "dd/mm/yyyy" will barf on danish settings to, cause
of the date separator, something like this "mm\/dd\/yyyy" can then be
used.
More info can be found here http://allenbrowne.com/ser-36.html

I think you should be able to utilzie the parameters collection
of the object type you're using, even on dynamic SQL. An NG
dedicated to C# would probably be of more assistance with regards
of actual syntax.

--
Roy-Vidar

Nov 13 '05 #3

P: n/a
RoyVidar wrote in message <mn***********************@yahoo.no> :

I said:
Just note that another format suggestion showed "dd/mm/yyyy"
will barf on danish settings to

But meant "mm/dd/yyyy" -> "mm\/dd\/yyyy"

--
Roy-Vidar

Nov 13 '05 #4

P: n/a
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.

"RoyVidar" <ro*************@yahoo.no> wrote in message
news:mn***********************@yahoo.no...
RoyVidar wrote in message <mn***********************@yahoo.no> :

I said:
Just note that another format suggestion showed "dd/mm/yyyy"
will barf on danish settings to

But meant "mm/dd/yyyy" -> "mm\/dd\/yyyy"

--
Roy-Vidar

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.