Our company is pushing out a company wide production & quality system, this is going across 4 different sites. In the SQL server tables, the date/time field shows up as a date/time field, however, in my ODBC linked table access database, the date/time field somehow got switched to a text field. I need this to be a date field to run reports by date. How do I fix this?
Date Time in SQL Tables changed in Access Tables (1 Viewer)
- Thread starter Jul
- Start date
Ranman256
Well-known member
- Local time
- Today, 13:17
- Joined
- Apr 9, 2015
- Messages
- 4,337
Ive never seen a date field link in as string. IS it linked as an external table?
1. You can try relinking the table.
2. you can build a 'base' query on the table and convert the string to a date via:
CDate([DateString])
base all your other queries off this query.
1. You can try relinking the table.
2. you can build a 'base' query on the table and convert the string to a date via:
CDate([DateString])
base all your other queries off this query.
The company that is designing our program said they had never seen it happen either. I will ask what field type they have selected for the date/time field. If I run a query to convert the dates to a date/time field in my ODBC database, would that automatically run to update the dates since this is a live system with information populating into it at least every hour?
You were correct, they have used DateTime2(7) as the datatype. Is there a way you know of that can convert that, which is now a text field into a date/time field?
Thanks for your help!
Minty
AWF VIP
- Local time
- Today, 18:17
- Joined
- Jul 26, 2013
- Messages
- 10,371
Moving forwards I personally would get them to change the datatype on the Server to datetime even though it is not the newest preferred datatype.
I can't see them ever dropping it, the only benefit to datetime2(7) is a greater potential range of both time accuracy and date longevity. Converting this in a query will add unnecessary complication to your system.
I can't see them ever dropping it, the only benefit to datetime2(7) is a greater potential range of both time accuracy and date longevity. Converting this in a query will add unnecessary complication to your system.
- Local time
- Today, 13:17
- Joined
- Feb 19, 2002
- Messages
- 43,271
The best choice is to get the DBA to change the data type to DateTime. Remind him that Excel will have exactly the same problem as Access since Excel uses the same archaic default ODBC driver from around the year 2000.
However, you can use a newer ODBC driver that can "see" the newer data types correctly.
I am using ODBC Driver 11 for SQL Server. version 13 is available also. The problem with using a new driver is that you have to have it distributed to all the users as well. Your IT support can help with this. They can make it part of their standard push package so that all users get it.
The worst choice is to attempt to fix this by formatting in your queries. You will always be behind the eight-ball and all new developers for your app will get bitten at least once by this issue. DON'T DO IT!!!!!
However, you can use a newer ODBC driver that can "see" the newer data types correctly.
I am using ODBC Driver 11 for SQL Server. version 13 is available also. The problem with using a new driver is that you have to have it distributed to all the users as well. Your IT support can help with this. They can make it part of their standard push package so that all users get it.
The worst choice is to attempt to fix this by formatting in your queries. You will always be behind the eight-ball and all new developers for your app will get bitten at least once by this issue. DON'T DO IT!!!!!
Thanks everybody. We ended up updating the driver on my computer, and will have to do the same with all users of the system eventually. Once the driver was updated, the dates formatted to date/time fields instead of text fields. Thanks again...Now on to my next issue..... 
