Append from Access to SQL failing on date fields.

Hello,

I'm moving an Access database to SQL using the Wizard.  All the tables convert OK apart from two.  I've discovered it is related to date fields - if I copy the tables, remove all the date fields and then try it works fine.

I've tried migrating just the table structure and using an append query but this errors with an "Overflow" message.  Again if I do the same with the date fields removed it works.

Can anyone suggest a solution for this.

Thanks


LVL 1
Petermcg001Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
peter57rConnect With a Mentor Commented:
So the problem is with he data values , not with the structure?

SQL Server ha a narrower range of acceptable dates than Access.  If you have dates in Access before 1753 these will not convert to sql server.  Access goes back to year 100.
0
 
devlab2012Commented:
in sql convert dates to a string in the format yyyy-mm-dd. In sql this format always works fine. Also, check between smalldatetime and datetime data types in sql server.
0
 
Petermcg001Author Commented:
It was data related with some very silly dates in the table.  Feel pretty silly TBH!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.