Re: Date problem

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Douglas J. Steele (NOSPAM_djsteele_at_NOSPAM_canada.com)
Date: 10/03/04

  • Next message: Chris Barber: "Re: Date problem" 
    Date: Sun, 3 Oct 2004 07:47:25 -0400

    A correction first. Your dates are NOT being "stored in UK dd/mm/yyyy
    style.". Dates are stored as 8 byte floating point values, where the integer
    part represents the number of days relative to 30 Dec, 1899, and the decimal
    part represents the time as a fraction of a day. They can be formatted
    however you like for presentation, and that's the only place where
    dd/mm/yyyy enters into it.

    If you're using SQL statements to insert the dates into your tables, the
    dates MUST be in mm/dd/yyyy format, regardless of what your Short Date
    format has been set to. (Okay, this isn't strictly true: you can use any
    unambiguous format, such as dd mmm yyyy or yyyy-mm-dd. The point is, dates
    in dd/mm/yyyy format are going to be interpretted as being in mm/dd/yyyy
    format unless dd is 13 or higher. And if you're only using a 2 digit year,
    there's even more chance of error.)

    While they're aimed at Access programmers, you still might find it useful to
    read Allen Browne's "International Dates in Access" at
    http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have at
    http://members.rogers.com/douglas.j.steele/SmartAccess.html 

    -- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Stephen Bowyer" <stephen.bowyer@nospamdsl.pipex.com> wrote in message
news:415fc6ce$0$29944$cc9e4d1f@news.dial.pipex.com...
> VB5 front end for an Access 97 database.
> The app displays a date from one table on a form, which can be modified by
> the user.  The date is then entered in a new or modified record in another
> table.  However in the second table the year is being stored as '2020'
> instead of 2004.  This only happens with a few records, not all.
> I guess the fact that 2004 is always converted to 2020 should give me a
clue
> as to what is causing this bug, but I can't see it.
> Dates are stored in UK dd/mm/yyyy style.
> Any help appreciated.
>
> Steve.
>
>
  • Next message: Chris Barber: "Re: Date problem"

    Relevant Pages

    • Re: Comparing SQL Server and User dates
      ... > dd/mm/yyyy or mm/dd/yyyy and when comparing it gives error. ... >> - Use some sort of calendar script so there can be no ambiguity ... > dates in dd/mm/yyyy what ever the system format and again if system format ... >>> dates in SQL server and thankful to them who replied. ...
      (microsoft.public.inetserver.asp.general)
    • Re: Date problem (need to use DD MM YYYY instead of MM DD YYYY (Australia)
      ... you can use the Aussie/British format of dd/mm/yyyy by ... defining that in the Windows Control Panel under Regional Settings. ... However, in a SQL clause, you must use the American format mm/dd/yyyy. ...
      (microsoft.public.access.queries)
    • Re: Date format error
      ... you cannot format the date as dd/mm/yyyy and expect it to work reliably. ... Doug Steele, Microsoft Access MVP ...
      (microsoft.public.access.modulesdaovba)
    • Re: Date filtering
      ... It is possible to work with Access dates in a country that uses dd/mm/yyyy. ... that you follow the expected date format in SQL clauses and VBA code. ... > variant in European order) the following will not work ...
      (microsoft.public.access.modulesdaovba)
    • Re: Issue in date formats
      ... > hvaing SQL server database to excel. ... I have even set the format of that cell as dd/mm/yyyy. ... But it is showing in MM/DD/YYYY format in Excel sheet. ...
      (microsoft.public.excel.programming)