SQL Server Migration Assistant for Access: Date data type

1
votes

I have been using SQL Server Migration Assistant for Access to migrate an access database to SQL Server. However, I need to convert data type "Date" in access to the type Date in SQL Server. When looking at the data type mappings options in SSMA I only have the options for datetime, datetime2, datetime2(precision) and smalldatetime. How do I get "date" mapped to the target type date in SQL Sever? Is this supported at all? I really don't want the time (hours and seconds) to be stored for some columns such as for the columns such as "leaving date".

Many thanks.

Type Mappings in SQL Server Migration assistant

1
sql-serverms-accessetl
blogs.msdn.com/b/ssma/archive/2011/03/06/… I found this. Maybe this etl doesn't support Date datatype of SQL. But you can always write a query later to convert datetime to date, in case you have access on database. - Deep Kalra
So you mean to write a custom query to convert a column to date? - Kris Van den Bergh
Yes, after the ETL has loaded the data, in the SQL Server you can make your table structure to have an additional calculated column which is on 'date' type and takes value from your datetime column. That will definitely work but don't know if this is the best way as I haven't done this before. - Deep Kalra

1 Answers

3
votes

What you are referring to as Access' 'Date' data type is actually Date/Time and is compatible with SQL Server Datetime, so use that. You could also use smalldatetime, if your data will always be within the valid date range for smalldatetime. If you are converting your Access BE and retaining an Access FE, be careful not to use any SQL Server data types that Access will not recognize. E.G. datetime2, Date (which SQL Server 2012 does have).