Date Translation Issue - Specific Windows 7 Machines

I am witing a web frontend to a backend MSACCESS database via ODBC. At some point int he future I will replace the database back end with a ‘proper multiuser one’ :slight_smile: but currently its not an option.

[code]//convert date from sql format to string to display in field
//YYYY-MM-DD to dd/mm/yyyy

dim d as new date
if fromdate="" then
return “”
else
d.SQLDate=fromdate
return cstr(d.ShortDate)
end if[/code]

The above method takes a sql date as a passed string ‘fromdate’ and coverts it to UK format. On my Windows 10 machine, and my Windows 7 VM, all works fine and the translation is correct. On the office WIndows 7 machines the date comes back wrong, even though Date/Time settings in control panel show the correct format. If I do it the ‘quick and dirty way’, as below it works but I am trying to use the language.

dim yyyy as string dim mm as string dim dd as string dim thedate as string if fromdate="" then return "" else yyyy=left(fromdate,4) mm=mid(fromdate,6,2) dd=mid(fromdate,9,2) thedate=dd+"/"+mm+"/"+yyyy return thedate end if

Its purely to display the dates retrieved in a local format and enter dates in a local format, when records are saved a reverse of the above method, ‘tosql’ puts it back in the DB as an SQL date.

Any suggestions as to why the ’ Win 7 office machines’ display the date wrong, for example;

28/04/1966 comes back as 4/28/1966

mm and dd are transposed and the 0 is stripped.

Are you getting the d.shortdate format that the SERVER is set up with, rather than the PC?

American format.

I was indeed - note to self, I must remember that where my web app sits is the important computer :slight_smile:

Too quick in saying this was the answer. Came into the office today (logged into server remotely yesterday and tested on server after making date change - all good, was not before). Logged in front of my own machine, date issue still remains on local machine, yet both server and local machine now have correct date format. I just don’t know where the app is getting its date format from.

i seem to remember on Access db, where we do a date range with SQL, you need to use the american format.
It does not matter how the setup for date format on your machine.

“SELECT * FROM tblStock WHERE DateCreated BETWEEN #01/01/2018# AND #01/31/2018#”
to get all stock create in jan 2018

Inside Access, the data is stored as a datetime.
If I read the post correctly…
OP Reads that, formats it as shortdate for display, and sees the wrong format.
Not the Access display format of #01/31/2018#

Thanks for both responses. The data is stored in the DB as SQL DateTime, and if I view it with Razor SQL, or even in access (with formats turned off) it shows as SQL DateTime, and I can search no problems. LATEST UPDATE - its no longer working on the server now, its reverted to American Format, Windows Server 2012 R2 Essentials.

When I view it in the web browser on my Windows 10 machine, and a VM Win7 on my Windows 10 machine no issues.

All works as expected, it only gets mess up as described if I load in a web on the Windows 7 pro office machines. Its bizarre so until I can track down the issue, then it am using the ‘rather messy’ but working;

dim yyyy as string dim mm as string dim dd as string dim thedate as string if fromdate="" then return "" else yyyy=left(fromdate,4) mm=mid(fromdate,6,2) dd=mid(fromdate,9,2) thedate=dd+"/"+mm+"/"+yyyy return thedate end if