[AccessD] MySQL / VBA Now() format

[Stuart McLachlan]

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 
things.

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 
day).

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