Dear all,

I have written a ASP page to insert a date value "02/03/2005" (March 2 2005) from my ASP page via ODBC connection into Access file. When I open my Access, I saw that the value was changed to "03/02/2005".

Thus I copy my SQL query from ASP page, paste to Access program and run it. The value was "02/03/2005"

I don't know why it is difference result.

Please help.

Patrick Lee

While inserting data, use mm/dd/yyyy format..

Please check the following postings which might be useful:

http://p2p.wrox.com/topic.asp?TOPIC_ID=1431
http://www.15seconds.com/faq/Data%20Access/768.htm
http://www.developersdex.com/asp/mes...=576&r=3867407

Om Prakash

There are lots of posts in this forum covering this topic in great detail. The actual international standard I believe is yyyy/mm/dd

Anyhow - I am in Aussie, we use the dd/mm/yyyy format here when showing dates on a page and asking a user for a date.

When using SQL Server or Access I always insert using a function to enforce the mm/dd/yyyy format, then when showing dates on a page I run another function to force the dd/mm/yyyy format

Here are the functions. These will resolve any date problems you may be having either when inserting or displaying dates on a page.

When inserting (forces mm/dd/yyy):
amDate(YourDateValue)

  FUNCTION amDate(varDate)
    IF DatabaseType = iDT_SQLServer THEN
      IF isNull(varDate) OR Trim(varDate) = "" OR varDate = "Null" THEN
        amDate = "Null"
      ELSE
        amDate = "'" & Month(DateValue(varDate)) & "/" & Day(DateValue(varDate)) & "/" & Year(DateValue(varDate)) & " " & TimeValue(varDate) & "'"
      END IF
    ELSE
      IF isNull(varDate) OR Trim(varDate) = "" OR varDate = "Null" THEN
        amDate = "Null"
      ELSE
        amDate = "'" & Day(DateValue(varDate)) & "/" & Month(DateValue(varDate)) & "/" & Year(DateValue(varDate)) & " " & TimeValue(varDate) & "'"
      END IF
    END IF
  END FUNCTION

When you have pulled a date in a record set and want to force the dd/mm/yyy format (assumes you have a date value in the first element of the record set named rs):

auDate(rs(0))

  FUNCTION auDate(varDate)
      IF isNull(varDate) OR Trim(varDate) = "" OR varDate = "Null" THEN
        auDate = "Null"
      ELSE
        auDate = Day(DateValue(varDate)) & "/" & Month(DateValue(varDate)) & "/" & Year(DateValue(varDate))
      END IF
  END FUNCTION

Wind is your friend
Matt