[AccessD] MySQL / VBA Now() format
stuart at lexacorp.com.pg
Wed Jan 25 15:38:40 CST 2023
How Access displays a timestamp field, how Access stores it internally, how MySQL
displays it by default, how MySQL stores it and how ODBC treats it are all entirely different
You are correct that how Access DISPLAYS a timestamp field by default uses your regional
settings, but you can override that by setting the format in the local table definition, the QBE
grid or the control on a form or report that displays the timestamp..
Access actually stores a DATETIME (including ODBC returned TIMESTAMPs as a 4 byte
Double (whole number represents date and decimal part represents time as a fractional of a
MySQL stores a TIMESTAMP as a four byte number (plus 1-3 additional bytes for v 5.6.4
and above if fractional seconds are stored). I think that the 4 bytes use the same structure
as a WIndows FileTime structure (but I'm not sure about that). Note that TIMESTAMPs are
actually stored as UTC and are converted to/from local time when accessed.
ODBC passes DateTimes/TimeStamps in a standard way regardless of how the source or
destination store or display them and does all necessary conversions internally.
Bottom line: How Access displays a Date/Time is irrelevant to working with datetime or
timestamp data in an ODBC linked table.
There is something else going on here if Now() and Format$(Now(),"dddd-mm-yy hh:nn:ss")
are not updating your timestamp.
On 25 Jan 2023 at 15:34, kost36 at gmail.com wrote:
> Hi Stuart,
> I think that ms access timestump field format depends on my regional
> settings which by default is "dd-mm-yyyy HH:mm:ss" When I use
> "yyyy-mm-dd HH:mm(or nn):ss" as format on an unboud text field it
> works fine. But it doesn't work on the connected via ODBC field. It
> always returns to "dd-mm-yyyy HH:mm:ss" Thank's
More information about the AccessD