[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
> 
> 
> -----Original Message-----
> From: AccessD <accessd-bounces+kost36=gmail.com at databaseadvisors.com>
> On Behalf Of Stuart McLachlan Sent: Wednesday, January 25, 2023 12:33
> AM To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com> Subject: Re: [AccessD] MySQL / VBA
> Now() format
> 
> Oops, that should of course be "HH:mm to hh:nn" with colons.
> 
> On 25 Jan 2023 at 8:29, Stuart McLachlan wrote:
> 
> > 
> > try changing HH-mm to hh-nn :)
> > 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>