Access to SQL Server date import problem

[david147]

When I import an access database into SQL server I'm getting import error which is due to the dates in access being converted to US date format mm/dd/yy.

The access & SQL server databases & windows servers are all in UK date type format dd/mm/yy so I can't understand why this is happening and how I can get the import to work correctly.

I want to avoid having to do a manual conversion as there are lots of date columns.

 

[Uncle Gizmo]

I don't believe there is an issue if you use the upsizing wizard. However there is an issue with the range of dates in MS Access and MS SQL server being different. MS Access Date /Time field range is Jan 1st 100 to December 31st 9999 , SQL server date / time data has a range Jan 1st 1753 to December 31st 9999.

If you have a date before 1753 you will get an error. It is worth checking because typographical errors are very common especially on computers with different local settings. If any data is out of range, data will not be upsized.

 

[david147]

I don't believe there is an issue if you use the upsizing wizard. However there is an issue with the range of dates in MS Access and MS SQL server being different. MS Access Date /Time field range is Jan 1st 100 to December 31st 9999 , SQL server date / time data has a range Jan 1st 1753 to December 31st 9999.

If you have a date before 1753 you will get an error. It is worth checking because typographical errors are very common especially on computers with different local settings. If any data is out of range, data will not be upsized.

No. This doesn't explain it. On the SQL server side there is a data preview of the access table. A field in access with the date '21/1/2005' is shown in the SQL server preview as '1/21/2005' and if you import it fails.

 

[Uncle Gizmo]

When I import an access database into SQL server

Have you tried the upsizing wizard though? I don't think there are any issues regarding the date if you use the upsizing wizard.

 

[david147]

Have you tried the upsizing wizard though? I don't think there are any issues regarding the date if you use the upsizing wizard.

I didn't, but I have now. There are some errors in it which I need to go through but the dates are right.

Thanks for the help.