access to sql date/time dts error

[ski2sun]

I have a SQL datetime format(8) column that I am trying to import data to, from access, with date/time property, medium time. Why wont it go into the new Sql table. i tried text also....msg given was...
error at destination for row number 1. Errors encountered so far in this task: 1. Insert error, column 4 ‘calltime, dbtype-dbtimestamp), status 6. Data overflow, Invalid character for cast specification.

 

[sguslan]

Is there any historical datetime data in Access, i.e. dates before the year 1753 ?

SQL books online states that "Date and time data is stored using the datetime and smalldatetime data types in Microsoft® SQL Server™ 2000. Use datetime to store dates in the range from January 1, 1753 through December 31, 9999 (requires 8 bytes of storage per value). Use smalldatetime to store dates in the range from January 1, 1900 through June 6, 2079 (requires 4 bytes of storage per value)."

 

[ski2sun]

they are all date/time, year 2000 forward...can you make a suggestion...thank you

 

[SQLSister]

Are you positive you are trying to convert to a datetime field and not a timestamp field? Also is there any non date data in the Access table? These are the two most common errors when converting that I can think of.

The only other thing I can think of would be to take the formatting off the Access table and then try to convert it.

 

[SQLBill]

ski2sun,

You say you have a "...SQL datetime format(8) column...". MS SQL Server does not have a syntax (that I'm aware of) that allows you to set the length of the datetime value.

IE:
CREATE TABLE mytable AS
MyDate DATETIME
, MyData VARCHAR(80)
<etc.>

Is your database really a Microsoft SQL Server database?

-SQLBill

 

[klausbert]

I suggest using a &quot;VBScript transform&quot; (instead of a Source->Dest &quot;Copy Column&quot; try reformatting the source as a character string of the form: &quot;yyyy-mm-dd&quot; or &quot;yyyy-mm-dd hh:mm:ss&quot; and assign this to the Dest column.

 

[ski2sun]

thanks so much all for your notes..will check all your suggestions....