Date Time in SQL Tables changed in Access Tables

[Jul]

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?

 

[Ranman256]

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.

 

[Minty]

I bet they have used DateTime2 as the datatype.
Access doesn't like it, and treats it as text.

DateTime and SmallDateTime are linked correctly.

 

[Jul]

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?

 

[Jul]

I bet they have used DateTime2 as the datatype.
Access doesn't like it, and treats it as text.

DateTime and SmallDateTime are linked correctly.

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]

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.

 

[Ranman256]

converting in a query is NOT complicated.

 

[Jul]

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.....