0

hopefully I can explain this problem in enough detail for someone to let me know of a possible solution, or at least let me know there isn't a fix (other than the one I mention below).

Ok, I am migrating from the following environment:

Windows XP/Access 2003/Classic ASP/IIS

to:

Windows 10 Pro/Access 2016/Classic ASP/IIS

Basically I have a local website running in IIS written in Classic ASP. The pages run in a web browser and there are plenty of inserts and updates occurring in an Access DB. The Win XP environment is setup as located in Australia, however I have all formatting as US based, so that all formatting occurs in "mm/dd/yyyy".

In Windows XP, I can run a query update in ASP as follows treating the date as a string:

    theDate = Now()
    SQLStmt =   "UPDATE tTable SET DateField ='" & theDate & "' WHERE DateID=1"

and it works just fine, as in if the date is October 1st 2017 it will appear in the Access DB table as 10/01/2017. The date type of the date field (in the above example, DateField) is of type "Date/Time".

So, I installed Windows 10 Pro, setup IIS with Classic ASP, created a virtual directory, moved over all the files from Win XP, and went about changing all the date settings throughout Win 10 to be US format. I have also changed the "Locale ID" under "Configure properties for ASP applications" to 1033 in IIS. Access DB is being used without converting to Access 2016 (so it can be moved back to Win XP environment if need be).

However, running the above query results in the date going into Access DB as "01/10/2017".

So I setup the following code to test out the date formatting:

    theDate_01 = Now()

    theDateTime = FormatDateTime(theDate_01,3)

    theDate_02 = Year(theDate_01)&"-"&Month(theDate_01)&"-"&Day(theDate_01)&" "&theDateTime

    SQLStmt =   "UPDATE tDateTest SET "
    SQLStmt =   SQLStmt & "theDate='" & theDate_01 & "'"
    SQLStmt =   SQLStmt & " WHERE DateID=1"
    Set RS = conntemp.execute(SQLStmt)

    SQLStmt =   "UPDATE tDateTest SET "
    SQLStmt =   SQLStmt & "theDate='" & theDate_02 & "'"
    SQLStmt =   SQLStmt & " WHERE DateID=2"     
    Set RS = conntemp.execute(SQLStmt)

%>

Basically I have a table called tDateTest in Access, with two columns DateID and theDate.

The result from running the above code is that theDate_01 is inserted incorrectly (that is, in AU date format) whilst theDate_02, because I've turned it first into yyyy-mm-dd format, does get inserted correctly.

Now with hindsight I would have done this whenever I was updating/inserting a date, however I am in the situation where I have many pages of code with many date updates/inserts that would need to be changed.

Is there any way to have the environment in Windows 10 behave the way it has been in Win XP, that a query with a date string simply inserts/update into an Access date/time field in the format I've specified throughout Win 10/IIS, in US format?

Daniel A. White
  • 187,200
  • 47
  • 362
  • 445
jupedelupe
  • 1
  • 1
  • The `FormatDateTime()` function is based on the system locale of the server. – user692942 Nov 01 '17 at 10:37
  • Possible duplicate of [Format current date and time](https://stackoverflow.com/questions/22574092/format-current-date-and-time) – user692942 Nov 01 '17 at 10:46
  • Possible duplicate of [Microsoft Access Date Format](//stackoverflow.com/q/11021668) – user692942 Nov 01 '17 at 10:52
  • I should also add that both theDate_01 and theDate_02 render correctly in a browser, as 10/01/2017. It is only when theDate_01 is updated in the database that it appears as 01/10/2017 within Access. – jupedelupe Nov 01 '17 at 13:13
  • Could you post the connection string used by the Access DB with any specific server names, users and passwords redacted? – user692942 Nov 01 '17 at 13:25
  • Sure: theDBDSN = server.mappath("test.mdb") theDB="PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" theDB=theDB & theDBDSN& ";" set conntemp=server.createobject("adodb.connection") conntemp.open theDB – jupedelupe Nov 01 '17 at 13:43

1 Answers1

0

First, you might be able to simply do:

SQLStmt = "UPDATE tTable SET DateField = Now() WHERE DateID=1"

If not, force the format and use octothorpes:

SQLStmt = "UPDATE tTable SET DateField = #" & Format(theDate, "yyyy\/mm\/dd") & "# WHERE DateID=1"
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Hi, and thanks. I definitely know I can do the second formatting example you note (and the first example doesn't solve it), but what I'm trying to see is if there's a way to not have to do this as per how it has worked in Win XP and save myself have to change the code in multiple pages/sections. – jupedelupe Nov 01 '17 at 10:35
  • For sure, the first will solve it - I just can't recall if `Now()` is accepted in your environment. – Gustav Nov 01 '17 at 10:46
  • You don't escape forward slashes in VBScript strings. – user692942 Nov 01 '17 at 11:07
  • Yes you do, or it will be read (and converted) as the _date separator_ which will be localised. – Gustav Nov 01 '17 at 11:22
  • @Gustav where talking about VBScript strings right? I've never seen forward-slashes escaped ever and I've used VBScript for over a decade. Also VBScript doesn't have a `Format()` function the closest thing to that is `FormatDateTime()`. See [Functions (VBScript)](https://msdn.microsoft.com/en-us/library/3ca8tfek(v=vs.84).aspx) – user692942 Nov 01 '17 at 11:40
  • Oh, then Format is moot. – Gustav Nov 01 '17 at 13:52