[Newbie UK Date problem] Ways atround this UK date format localisation/SQLselection issue?

[DC]

Im having a rather annoying SQL related problem with SELECT statemnets
and UK dates

The entire application is globalised to culture="en-GB" and
uiCulture="en-GB", and the access database is displaying and accepting
dates in UK format.

For some reason the statement is selecting the records with a date
before rather than after dtCurrDate.

dtCurrDate is produced via DateTime.Now and appears to be returning the
correct current date in uk format.

"SELECT * From SeminarList WHERE SeminarDate > #" + dtCurrDate +"# ORDER
BY SeminarDate";

Produces a set of records where SeminarDate is before todays date. Is
there something else I need to change?

EG: today the SQL produced was

SELECT * From SeminarList WHERE SeminarDate > #01/07/2005 00:00:00#
ORDER BY SeminarDate

and yet the diplay returns

Next Seminar - MSc dissertation presentations 2005
On 28/06/2005 at 11:30 in Sutcliffe Lecture Theatre (GU01)

Which obviously happened last week, is this a simple consequence of SQL
being inherantly US date formatted? Is there anything I can do about it?

--
_______________________________________________

DC

"You can not reason a man out of a position he did not reach through reason"

"Don't use a big word where a diminutive one will suffice."

"A man with a watch knows what time it is. A man with two watches is
never sure." Segal's Law

 

[yer darn tootin]

Try a sql CONVERT function on the date field in the query


SELECT * From SeminarList WHERE
CONVERT(varchar(12),SeminarDate,106) > '01/07/2005'
ORDER BY SeminarDate

 

[yer darn tootin]

Sorry, just noticed you wrote it was an ACCESS db - my answer was for
SQL server. Not sure if Access has any convert functions available.

 

[Nick Malik [Microsoft]]

I think that the SQL language in Access assumes US date order: mm/dd/yyyy

try this: convert your date string to use the USA format for the Select
statement. The rest should work as is.

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.

 

[Christof Nordiek]

Hi DC

instead of pasting your value into the SQL-Statement you should use
parameters.
Then the Dataprovider does all the convertion for you in the right way.
By that, you also can prevent SQL-injection attacks.

But I don't know if that's possible with Access.

Christof

 

[Brendan Reynolds]

See the following on-line help topic ...
http://office.microsoft.com/assistance/hfws.aspx?AssetID=HP010322871033

In particular, see the paragraph beginning ...

"When you specify the criteria argument, date literals must be in U.S.
format, even if you are not using the U.S. version of the Microsoft® Jet
database engine."