Date Format Not Respected (1 Viewer)

[PleasantB]

I've upgraded an Access 2003 application to Access 2016 and migrated the back-end to SQL Server.
Now all of my date fields are in the format yyyy-dd-mm hh:mm:ss despite the text box Format being set to Short Date.
I suspected this was due to the regional settings in Windows but when I checked region format, short date was specified as m/d/yyyy.

Does anyone know how to fix this?
Thanks in advance.

 

[pbaldy]

What is the data type of the field in SQL Server? What data type does the linked table think it is (put it in design view). What ODBC driver are you using? My gut is that the data type on the server isn't being recognized properly.

 

[PleasantB]

Thanks for the response.
Sorry for the delayed reply, I had to schedule some time to get onto the client's UAT environment as I was not experiencing this issue on my development environment.

The data type of the field on SQL Server is datetime2(0) (same as on my development environment).
The linked tables have the columns typed as Short Text (while on my development environment they are typed as Date/Time).
The SQL Server driver (version 10.00.16299.15) is used to interface with SQL Server (my development environment uses the same driver but newer version 10.00.18362.01).

The incorrect linked table column type seems to explain the incorrect date formatting but I'm unsure of why the data type is wrong to begin with.
Any ideas?

 

[pbaldy]

If you have control over the SQL Server tables, change the data type to datetime and relink the tables in Access. That should resolve the issue.

 

[Minty]

If you can't change that you will need to update to the latest driver ODBC Version 17 which I believe does support the newer date datatypes.

 

[PleasantB]

I do have control over the SQL Server tables so I will try datetime when I get access to UAT environment again next week.
I'm still puzzled as to why this issue is not present in my development environment though...

 

[pbaldy]

Perhaps your driver is new enough to recognize the new data type and theirs isn't. You could certainly update the driver used in the user's environment, but it would probably require you (or somebody) to touch each machine to install it.

 

[Pat Hartman]

In some cases when Access doesn'trecognize a data type, it shows you a string and so SQL Server converts the "date" to a string because Access doesn't recognize that data type. If you don't change the data type to DateTime or a newer driver installed on not only your PC but EVERY USER's PC also, doesn't work, you can use the date but remember it will always be treated as a string not a date. It also might not be updateable.

Just FYI, If you don't want time in your date field, use Date() rather than Now() to set the current date.

Formatting at the table level that hides the true value is poor practice. If you force the format to be short date but you're sloppy about using Now() when you mean Date(), you'll be back with questions about why your queries or If statements don't work.

 

[CJ_London]

see this link for the different date field types

SQL Server Datetime vs. Datetime2 – SQLServerCentral

Read a short comparison of datetime and datetime2 types. Learn about their structure, as well as the advantages and disadvantages of each.

www.sqlservercentral.com

datetime2 has additional accuracy after seconds. What would work is to remove them - something like

cDate(left(myfield, instr(myfield,".")-1)

 

[Minty]

I've been using smalldatetime in SQL for quite some time where I only want as simple timestamp/date field.
It seems to be understood by most of the drivers.

 

[PleasantB]

Sorry for the very late follow up but there were several delays and corporate policy roadblocks on the client's end since I originally posted.

I stubbornly wanted to avoid changing the column data type so I went the route of trying to get the ODBC driver recognizing datetime2.
First, I tried the ODBC Version 17 Driver but could not get it to connect to the SQL Server instance so I eventually gave up on that.
Next, I tried to match the client machine's SQL Server Driver to that of my development machine but that didn't solve the problem.

I'm finally throwing in the towel and am going to change the column type to datetime.
It still baffles me that datetime2 is recognized in my environment but not on theirs, I'll wonder about this for many days to come.

Anyway, I just wanted to give an update rather than leave this thread hanging forever.

 

[Isaac]

I've been using smalldatetime in SQL for quite some time where I only want as simple timestamp/date field.
It seems to be understood by most of the drivers.

I have a hard time reconciling these 3 things:
- MS pushing everyone to use datetime2, even though 1/2 of the 'benefit' is extra nanosecond precision that most data does not need to be managed to
- supposedly, datetime2 is ANSI SQL compliant
- but, datetime2 doesn't play well with Access, which it seems most things ansi sql compliant would.

 

[isladogs]

Where

I have a hard time reconciling these 3 things:
- MS pushing everyone to use datetime2, even though 1/2 of the 'benefit' is extra nanosecond precision that most data does not need to be managed to
- supposedly, datetime2 is ANSI SQL compliant
- but, datetime2 doesn't play well with Access, which it seems most things ansi sql compliant would.

I've not heard of any push from MS to use DateTime2. Its available for anyone who needs it ...along with support for bigint datatype.
However, as I understand things, both work perfectly well with Access provided that your Access version is recent enough to support that feature and that the feature hasn't been disabled by the user.

 

[Isaac]

I've not heard of any push from MS to use DateTime2

The recommendation is everywhere, here is a short blurb I found after a brief search.
In their documentation on both datetime and datetime2, the strong recommendation is not to use datetime unless you are forced to in consideration of consuming apps

However, as I understand things, both work perfectly well with Access provided that your Access version is recent enough to support that feature and that the feature hasn't been disabled by the user.

What I hear is that 2010 provided "limited support" for it, but many people still have problems with it even after that point.
(Surely you noticed the theme of this thread?)

 

[isladogs]

The datetime quote you provided states ...for new work.

Neither bigint nor datetime extended datatypes were supported in A2010.
Bigint support was added in A2016 and DateTime2 in A365 version 2009 or thereabouts.

Specifically, SQL datetime2 fields were treated as text prior to the new datetime extended datatype

 

[Isaac]

The datetime quote you provided states ...for new work.

Neither bigint nor datetime extended datatypes were supported in A2010.
Bigint support was added in A2016 and DateTime2 in A365 version 2009 or thereabouts.

Specifically, SQL datetime2 fields were treated as text prior to the new datetime extended datatype

Yep - Agreed.