[AccessD] Date Comparison Problem

    Thanks Gustav!  I agree - Considering the implications associated with languages other than English, "m/d/yyyy" format mentioned by you would be safest.

    Alternatively, explicit conversion to DateValue() within the SQL string as suggested by Stuart.

A.D.Tejpal
---------------

  ----- Original Message ----- 
  From: Gustav Brock 
  To: accessd at databaseadvisors.com 
  Sent: Friday, March 31, 2006 14:37
  Subject: Re: [AccessD] Date Comparison Problem


  Hi A.D.

  Yes, in fact most other languages than English will fail - even worse it may only fail for some months.

  For example, the Danish abbreviations for months equal the English except for one month, October, which is okt.
  Thus, using that, an October date will be formatted as 3-okt-2006, which when put in an SQL string as #3-okt-2006# will raise a syntax error. Note that even the slash has been replaced with the localized date separator which here is "-".

  The use of d/m/yyyy is highly recommended as that is standard SQL which will work for any decent SQL engine. Most modern SQL engines with MaxDB (SAP DB) in front accepts or even prefers the ISO format with 24 hour time of yyyy-mm-dd hh:nn:ss or even yyyymmddhhnnss.

  /gustav

  >>> adtp at airtelbroadband.in 30-03-2006 21:24 >>>
  Gustav,

      I agree that "m/d/yyyyy" format for concatenation into a string should always give consistent results. Similar have been the findings with adoption of "dd-mmm-yyyy" format.

      One advantage of non-ambiguous format is that even if the programmer happens to slip up from standard sequence, the result still gets interpreted correctly. For example, all the following permutations will work OK:
      dd-mmm-yyyy, mmm-dd-yyyy, dd-yyyy-mmm, mmm-yyyy-dd, yyyy-mmm-dd, yyyy-dd-mmm.
      
      Are you in a position to test out and verify whether there is any specific situation where non-ambiguous format represented by "dd-mmm-yyyy" is not found to give consistent results ? Do you have some language other than English in mind ?

  Best wishes,
  A.D.Tejpal
  ---------------

    ----- Original Message ----- 
    From: Gustav Brock 
    To: accessd at databaseadvisors.com 
    Sent: Thursday, March 30, 2006 23:48
    Subject: Re: [AccessD] Date Comparison Problem


    Hi A.D.

    That may not work as the literal month names are localized as well.

    /gustav


    >>> adtp at airtelbroadband.in 30-03-2006 20:04 >>>
     
    StrSQL = StrSQL & " WHERE TDate Between  #" & _
                     Format(StartDate, "dd-mmm-yyyy") & "#"