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’ 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;
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