SSMA unable to convert Date/time in MS Access to Datetime in SQL Server-CodePudding

I'm migrating my Access backend to SQL Server using SSMA. The SSMA is throwing an error when making changes in the mappings. I have the Date/Time field in Access which needs to be DateTime in SQL to work. Now SSMA by default sets mappings of date to datetime2 which when run migrates successfully to SQL but is shown as a short text field in Access. When I try to manually update the mappings in SSMA from datetime2 to DateTime and start the migration I receive the following error(Please find the details of the error through the link below):

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

Table in Access:

[AutoNo][CNR][Datefilecreated][Datemodified][DateChangeTeamID]
2227    2620    11/04/2018  11/05/2022  11/05/2022
3078    3513    27/11/2018  11/05/2022  11/05/2022
3673    4113    24/12/2020  14/12/2021  
3395    3835    25/11/2019  07/12/2021  12/10/2021
3238    3663    27/05/2019  07/12/2021  
3186    3610    08/04/2019  07/04/2022  
3331    3771    23/09/2019  07/12/2021  
3128    3565    26/02/2019  07/12/2021  12/10/2021
2047    1993    28/04/2015  24/01/2022  28/07/2018
2767    3160    06/09/2017  07/12/2021  28/07/2018
2356    2749    25/04/2016  07/12/2021  12/10/2021
1318    1264    14/11/2011  26/01/2022  28/07/2018
2698    3091    03/07/2017  07/12/2021  12/10/2021
2551    2944    09/01/2017  07/12/2021  12/10/2021

The table in SQL when migrated from Access with datetime2

Auto number CNR Date file created   Date modified   DateChangeTeamID
4954    4954    2022-05-30 00:00:00.000 NULL    2018-07-28 00:00:00.000
3927    42281   2005-02-01 00:00:00.000 2017-01-11 00:00:00.000 2018-07-28 00:00:00.000
3928    42287   2002-04-01 00:00:00.000 NULL    2018-07-28 00:00:00.000
3929    42288   2005-05-05 00:00:00.000 2017-01-11 00:00:00.000 2018-07-28 00:00:00.000
3930    42289   2005-05-04 00:00:00.000 NULL    2018-07-28 00:00:00.000
3931    42290   2005-05-05 00:00:00.000 NULL    2018-07-28 00:00:00.000
3932    42291   2004-03-29 00:00:00.000 NULL    2018-07-28 00:00:00.000
3933    42292   2002-04-01 00:00:00.000 NULL    2018-07-28 00:00:00.000

SSMA Error details when migrating with destination mapping as DateTime instead of datetime2

CodePudding user response：

I would consider running a query on the access (client side), and looking for some bad dates in that column.

Null dates should work just fine. It is possbile that the access column is in fact NOT a datetime column?

And yes, it is MUCH preferred to setup to use datetime in SQL server as opposed to datetime2.

However, you CAN use datetime2 in access as linked tables, and it WILL see this correctly as a datetime (and not text). But for this to work, you MUST then install use link your tables using the newer ODBC drivers. (native 11 or later). The 2nd fallout of this choice is that then any and all workstations that plan to use Access with these linked tables ALSO must have that SAME native 11 (or 17 or 18) installed. So, you have to re-link your tables using the newer driver, and you also have to ensure that each workstation also has that newer ODBC driver downloaded and installed on each workstation.

Above will thus allow access to see/use the datetime2 columns correctly as date time in access and VBA code.

however, as a general rule, it sounds like you have some bad dates in your original data. I would create a query that looks for say a date less then 1960, or some such, and see if any records are returned. If such older dates are not valid, then update then with a query to null values.

Sounds to me, that there exists some bad date values in that data. You could ALSO migrate, then change the datetime2 back to datetime on the server side, but with lots of tables, and lots of date columns, that could be quite a bit of work.

Since this looks to be ONLY one table, then as noted, try a query on that table in Access that looks for dates older then what your data supposed to have. Clean those out, and migrate again.