December 1, 2021 at 6:52 pm
I created a date field [Datte] in SQL v2008. I then link with MS Access. No problem. Done this many times.
However, this time, when I open the table in MS Access, the date displays as: 2021-03-15 (yyyy-mm-dd)
All my other tables display like I want it: 03/15/2021 (mm/dd/yyyy)
The field attribute is date, not datetime or smalldate. I even deleted the column, saved, recreated and it still defaults to: 2021-03-15 (yyyy-mm-dd)
What changed? Why is the date displayed by default in this format? How do I change?
Thanks!
December 1, 2021 at 8:15 pm
Do you mean in table edit/view mode? You can open the table in design view and set the format to mm/dd/yyyy. Or in a form or report (in which case you can set the format on the field)?
The data isn't stored w/ formatting; it's just an attribute of presentation. 2021-12-01 is equivalent to 12/01/2021.
You said the field attribute is date. Columns have data types. Format is an attribute.
SQL Server 2008 does not have a date data type -- it has datetime & smalldatetime. What is the actual data type in SQL Server?
December 1, 2021 at 8:28 pm
SQL Server 2008 does not have a date data type -- it has datetime & smalldatetime. What is the actual data type in SQL Server?
SQL 2008 does indeed have the date data type. It was introduced in that version.
As for the display in Access, that's more a question for an Access forum. As Ratbak says, there is no format with the date data type as such. It's basically three bytes holding the number of days since 0001-01-01.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
December 1, 2021 at 8:52 pm
EDIT - disregard my post. Everything I said has already been in previous replies.
If memory serves, SQL Server presents the date in the format that is configured on the server. For example, if the server is configured for USA style dates, you will get it in mm/dd/yyyy format.
My thought in your case is that your server hosting SQL Server 2008 is configured for dates to be in yyyy-mm-dd format. That and/or Access has the ability to display dates in different formats and your other dates have been configured to display in mm/dd/yyyy format manually.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 1, 2021 at 8:55 pm
SQL Server presents the date in the format that is configured on the server.
SQL Server does not present the date in any format at all! SQL Server is a database layer, and is not involved with presentation matters.
You can use the convert function to convert to a string, but the result will always be the same no matter the language setting of the session. (The only language setting there is the default language for new logins.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
December 1, 2021 at 9:35 pm
@Erland - my mistake. I had meant "SQL Server Management Studio presents the date in the format that is configured on the server", not SQL Server. SQL Server stores the date in a similar way to how Excel does where it is just a number.
Is it always the same representation of the date no matter what region setting is set on the server? I know it isn't based on the region setting of the client as mine is configured for dd/mm/yyyy and a SELECT GETDATE() returns it in yyyy-mm-dd format. I am not certain what the date format is set to on the server, but there was a part of me that vaguely remembered reading that SSMS returns the date in the format set on the server... I would not be surprised if I was remembering things wrong though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 1, 2021 at 9:37 pm
I understand what you all are saying about the way dates are displayed.
Re: v2008 not having a date attribute. Mine does, unless you're distinguishing between v2008 and v2008 R2. I got the below using: SELECT @@Version
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) Mar 26 2015 21:18:04 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
I changed the field from "date" to "datetime" and the date displays as I wanted.
Thank you all or your input!
December 1, 2021 at 9:41 pm
@Erland - my mistake. I had meant "SQL Server Management Studio presents the date in the format that is configured on the server", not SQL Server.
I believe SSMS always present datetime values as YYYY-MM-DD without respecting the regional settings on the client, but I could be wrong on that point. My settings are YYYY-MM-DD, so I cannot really tell.
In any case, if SSMS would go by any setting, it would be a client-side setting, and not a server-side setting.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
December 1, 2021 at 10:28 pm
I missed that reviewing MS doc for data types. Thank you.
I worked in 2008 for a few years, but have not for a while now.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply