Migration Problem from Access to SQL Server (DateTime, datatype)

[da75]

I am having Problems importing data into SQL Server from Access database.

The column is in date/time, LongTime format in access.

I have tried copying it over just table to table and it doesn't work.

Can anyone reccomend how i copy this data over and the best datatype for the SQL Server column to be.

Thank You

 

[AWithers]

The date data types in both Access and SQL Server are compatible. Have your tried importing the table using Data Transformation Services (DTS). Remember in both Access and SQL server dates are stored as numbers with a decimal place represnting the time. The long time format you mention is purely formatting of the date-time value

HTH

Andy

 

[da75]

Hi Andy

I have been using DTS and the error i get relating to these columns is

Insert error, column 14 ('TimeStamp',DBTYPE_DBTIMESTAMP)
Status 6 Data Overflow. Invalid character value for cast specification.

Any Solutions

Thanks Danielle

 

[SQLSister]

TIMESTAMP is not a datetime field! Try converting it to DAateTime data type.

 

[AWithers]

Ummmm...sounds as though sql thinks the data is a string

perhaps you might want to try, in Access, removing the date-time (long time) format and try again

If that fails then, again in Access, try converting the date-time column to a double then in SQL add an extra colum to the table and update this column to the date value that relates to

e.g. somthing like this
update yourtable
set yournewdatefield = convert(datetime,yournumericaldatefield)