locked
Access database query problem. RRS feed

  • Question

  • While gets datas from Access database i have a problem. 

    this database have "date" column and property of this column is "string" type.

    When i send query i received datas but i found a problem  .

    in sql command there is this date 12.11.2008 (string)
    but after i received datas i see 11.12.2008 date too in datas.

    What is problem i dont know and can not find.
    Wednesday, December 31, 2008 8:49 AM

Answers

  • It has to do with regional settings. In some regions, the month comes first, in other regions the day comes first.

    But why are you storing dates in a string typed column. Internally when choosing the date datatype, the value will be stored as a number. This number will be converted to a date and correctly displayed according to the regional settings. Storing a date as a string is asking for problems.
    Ewald - Please remember to mark the replies as answers if they help.
    • Proposed as answer by Harry Zhu Friday, January 2, 2009 3:30 AM
    • Marked as answer by Harry Zhu Tuesday, January 6, 2009 3:55 AM
    Wednesday, December 31, 2008 9:47 AM
  • INSERT INTO MyTable(MyDate) VALUES (#12-31-2008#)

    As you can see, you must use the # (hash) character to pass date values to the query. You must use the order of day and month according to the local regional settings.

    To be able to help you better, please provide the code you are using.
    Ewald - Please remember to mark the replies as answers if they help.
    • Proposed as answer by Harry Zhu Friday, January 2, 2009 3:30 AM
    • Marked as answer by Harry Zhu Tuesday, January 6, 2009 3:55 AM
    Wednesday, December 31, 2008 10:49 AM

All replies

  • It has to do with regional settings. In some regions, the month comes first, in other regions the day comes first.

    But why are you storing dates in a string typed column. Internally when choosing the date datatype, the value will be stored as a number. This number will be converted to a date and correctly displayed according to the regional settings. Storing a date as a string is asking for problems.
    Ewald - Please remember to mark the replies as answers if they help.
    • Proposed as answer by Harry Zhu Friday, January 2, 2009 3:30 AM
    • Marked as answer by Harry Zhu Tuesday, January 6, 2009 3:55 AM
    Wednesday, December 31, 2008 9:47 AM
  • i changed type colums to date&time type but while sending query giving error i thin i send date veriable mistake format.
    For queries from Access database date format  which format veriable i must send?
    Wednesday, December 31, 2008 10:45 AM
  • INSERT INTO MyTable(MyDate) VALUES (#12-31-2008#)

    As you can see, you must use the # (hash) character to pass date values to the query. You must use the order of day and month according to the local regional settings.

    To be able to help you better, please provide the code you are using.
    Ewald - Please remember to mark the replies as answers if they help.
    • Proposed as answer by Harry Zhu Friday, January 2, 2009 3:30 AM
    • Marked as answer by Harry Zhu Tuesday, January 6, 2009 3:55 AM
    Wednesday, December 31, 2008 10:49 AM