SpaceCoastLife
asked on
Modify the format of a date field in a table with thousands of records to another date format
How do I convert a SQL Server db table (thousands of records) with a date field formatted as YYYY-MM-DD into a format of MM/DD/YYYY either in the same field or a new one?
ASKER
It is a date datatype in a table I'm importing into Access. In Access, when adding a criteria in a query using that table do I put the value in quotation marks or number sign i.e. "2020-05-10" or #2020-05-10#?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
So, this is an Access question. Once you import data into a database, where it came from is no longer relevant.
Same answer essentially applies. Access stores dates in an internal format. There is no display format associated with it. If you are storing it as a date in Access, then whatever the Access format is for querying a date applies. Seems like the # is the correct way, but I don't really use Access at all.
Same answer essentially applies. Access stores dates in an internal format. There is no display format associated with it. If you are storing it as a date in Access, then whatever the Access format is for querying a date applies. Seems like the # is the correct way, but I don't really use Access at all.
The best you can do is to leave the SQL Server column unchanged. It ensures no date ambiguity.
Use the Format() function at the Access side to format the output to whatever you need.
Of course, depending on your Access version the datetime column provides better compatibility.
To query the SQL table use the format "2020-05-10". It should be converted correctly. Just give it a try.
Use the Format() function at the Access side to format the output to whatever you need.
Of course, depending on your Access version the datetime column provides better compatibility.
To query the SQL table use the format "2020-05-10". It should be converted correctly. Just give it a try.
You have to know that SQL Server Management Studio always shows date in format YYYY-MM-DD.
But Access display the date based on the Windows regional settings of the current user.
So you have to understand that the display format of data is just the rendering of date and it can be different at different users but the date value itself is permanent.
However access accepts date filtering on your current date settings so: #MM/DD/YYYY#.
But Access display the date based on the Windows regional settings of the current user.
So you have to understand that the display format of data is just the rendering of date and it can be different at different users but the date value itself is permanent.
However access accepts date filtering on your current date settings so: #MM/DD/YYYY#.
ASKER
Thanks for the education!
If it is a string format, you would be best served by using substrings and rebuilding the string to get what you want, but it could easily be done in a single update.