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

Discussion in 'ASP .Net' started by DC, Jul 1, 2005.

  1. DC

    DC Guest

    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
     
    DC, Jul 1, 2005
    #1
    1. Advertising

  2. Re: Ways atround this UK date format localisation/SQL selection issue?

    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, Jul 1, 2005
    #2
    1. Advertising

  3. Re: Ways atround this UK date format localisation/SQL selection issue?

    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.
     
    yer darn tootin, Jul 1, 2005
    #3
  4. Re: [Newbie UK Date problem] Ways atround this UK date format localisation/SQL selection issue?

    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
    >
     
    Nick Malik [Microsoft], Jul 1, 2005
    #4
  5. Re: [Newbie UK Date problem] Ways atround this UK date format localisation/SQL selection issue?

    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
    >
     
    Christof Nordiek, Jul 1, 2005
    #5
  6. Re: [Newbie UK Date problem] Ways atround this UK date format localisation/SQL selection issue?

    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."

    --
    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
    >
     
    Brendan Reynolds, Jul 1, 2005
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Tony
    Replies:
    1
    Views:
    401
    .NET Follower
    Feb 10, 2004
  2. Brian Candy
    Replies:
    2
    Views:
    1,208
    Janaka
    Feb 18, 2004
  3. Olivier Matrot

    Localisation problem

    Olivier Matrot, Mar 8, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    470
    Olivier Matrot
    Mar 8, 2005
  4. Replies:
    2
    Views:
    3,436
    Abdoosh
    Sep 19, 2007
  5. bob

    Date Localisation

    bob, Sep 23, 2008, in forum: ASP .Net
    Replies:
    0
    Views:
    316
Loading...

Share This Page