Datetime field issue with Access 2k3 db converted to SQL 2k
Hello all - I used the upsizing wizard within Access 2k3 to create a new SQL db on my SQL 2k SP4 server. Everthing went smoothly, except the access Date/Time field converstion to datetime. When I open the tables containing the date & time fields from Enterprise Manager, they show with the correct separation of dates in one column, and times in another. But when I run the command below from QA, it comes back with two columns, each containing both dates and times.
"select * from <tablename>"
According to the developers (I'm the dba) this is causing issues with their application. I've found the articles below somewhat referencing the issue, but either cannot get to the full articles, or cannot get the formatting correct to resolve the problem.
Any help would be appreciated!
http://www.sqlmag.com/Article/ArticleID/43488/43488.html
http://www.sqlmag.com/Article/ArticleID/9026/sql_server_9026.html
http://www.windowsitpro.com/Article/ArticleID/15256/15256.html?Ad=1
Software/Hardware used:
Software/Hardware used:
ASKED:
August 17, 2006 4:37 PM
UPDATED:
August 21, 2006 12:03 PM
Answer Wiki:
Sounds to me like your developers are the ones who are confused. Both Access and SQL Server datetime fields store both the date and time, whether you enter both, or not. So, your query is working correctly. (Given a choice between a query and an Enterprise Mangler (EM) display, always go with the query results. EM lies.)
I'd say your dev guys added two datetime fields to their Access table and formatted one for date and the other for time. The wizard apparently noted this and somehow passed the info on to EM. But, what is actually stored is what MS calls a "dateserial" that contains all date AND time info. It will store default values, if none are provided for one or the other. I'd bet the field that is only supposed to have dates in it has midnight as the time for every record, for example.
This is assuming the developers didn't do something as totally amateur as using text fields to hold the dates and times. I suppose it's possible the upsizing wizard could convert those into datetime fields, if you told it to. Again, each column would have the same value, for each record, for the part not provided by the developers.
Tell them to open their base table directly, in design view, and verify the data type of these fields. If it's Date, have them remove all formatting from those fields, and then have them view the datasheet, still in the base table. They'll see what you're seeing in your query.
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you VERY much, DaveinAZ! The devs don’t believe me, and they want me to try importing into diff versions of SQL, but I’ll deal with that separately. I really appreciate your info, and the great explanation that went along with it!