«

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

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

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.

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.
--
"DC" <> wrote in message
news:da356g$ert$...
> 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
>

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

"DC" <> schrieb im Newsbeitrag
news:da356g$ert$...
> 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
>

See the following on-line help topic ...
http://office.microsoft.com/assistan...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."

--
Brendan Reynolds (Access MVP)

"DC" <> wrote in message
news:da356g$ert$...
> 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
>