[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 373
  • Last Modified:

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


0
Petermcg001
Asked:
Petermcg001
1 Solution
 
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
 
peter57rCommented:
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
 
Petermcg001Author Commented:
It was data related with some very silly dates in the table.  Feel pretty silly TBH!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now