Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: international dates

    I have a db on a uk server

    i am insert sql as follows (from asp)

    update custoemrs set sent=#11/10/2007# where idn='3362201'

    now it's going in the db as 10/11/2007

    and it's a uk server?
    anyway to insert dates in international format?

    how do i fix

    18/10/2007 went correctly

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Try using the universal date: 2007-10-11 (or 29971011)
    Let me know if that sorts the problem out
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2006
    Posts
    66
    does access accept dates in international format?

  4. #4
    Join Date
    Jun 2006
    Posts
    66
    i get type conversion failure

    how do you put dates in sql?

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    access partially handles sort-of ISO8601 dates/datetimes:
    yyyy-mm-dd
    yyyy-mm-dd hh:nn:ss


    access does not handle most of the rest, including:
    the T separator between date & time
    the abbreviated (and ISO-permitted and also very ugly) yyyymmdd
    the week notation yyyyWwwd

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jun 2006
    Posts
    66
    but this didn't work
    i did
    update customers set sent=2007-10-11 where idn='4949832'

    and it put in 08/06/1905

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    strSQL = "INSERT INTO tblX(dateY) VALUES('2007-10-21')"
    or
    strSQL = "INSERT INTO tblX(dateY) VALUES(format$(now(), 'yyyy\-mm\-dd'))"

    izy
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    or
    strSQL = "UPDATE customers SET sent = '2007-10-11' WHERE idn = 4949832"

    ?? is idn string or numeric, if string continue with your
    ....WHERE idn = '4949832'"

    izy
    currently using SS 2008R2

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    ? 2007-10-11
    1986

    ? format$(1986, "yyyy-mm-dd hh:nn:ss")
    1905-06-08 00:00:00

    currently using SS 2008R2

  10. #10
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    > update customers set sent=2007-10-11 where idn='4949832'
    >
    > and it put in 08/06/1905

    Because 2007-10-11 (subtraction) is 1986, and 1986 days from 30/12/1899 (0-date for Jet) is 08/06/1905 (or rather, as the US would have it 06/08/1905

    If you're on a direct (ADO) connection, you'd use single quotes as date separators. If you're going through linked tables, you use # as separator.

    update customers set sent=#2007-10-11# where idn='4949832'
    update customers set sent='2007-10-11' where idn='4949832'
    Roy-Vidar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •