November 14, 2004 at 6:34 pm
I have an Access database that I'm trying to move the data to SQL Server 2000 and there is a table that has a couple of columns defined as Date/Time with a format of hh:nn:ss. When I try to import the data into a column defined as datetime in SQL Server I get conversion errors. This columns are used to capture durations of events. Any idea of how to get the data to convert?
Thanks,
Kevin
November 15, 2004 at 2:38 am
You could:
Create a new column in access that matches the sql server datatype and convert the data into there. Then use this column as the source.
Or:
You could create your own datatype in sql server to match that in access and then use that column as the destination.
If all that fails:
Use a transformation script in dts to change the data to the type that a sql server datetime column expects.
November 15, 2004 at 7:24 am
KC-
You didn't mention what version of Access you are using, but if you have Access 2000 available I would also try the Upsizing Wizard from Access and "push" the table up. I have found in testing that it does a pretty good job of directly porting tables from Access into SQL Server. For example, the Upsizing Wizard will accurately convert an "AutoNumber" column in an Access table to int identity (1,1) in SQL. It also does a pretty good job of preserving keys and idexes, in contrast to pulling the tables up with DTS.
I just created a test table in Access 2000 that had an AutoNumber PK column and a date/time column formatted "hh:nn:ss". I added one record to the table with "2:30:00" in this column. Used the Upsizing Wizard to push the table up, "2:30:00AM" appears in the field in SQL. There were no errors on the transfer.
November 16, 2004 at 8:24 am
The real issue is that the DTS import defines an Access DateTime field as a SQL DateTime data type when it creates the table. You need to modify the SQL field to a SmallDateTime data type.
1: If your table is already created, Edit it and change the DateTime to SmallDateTime.
2: If you are newly creating it and using the DTS Import Wizard, when you reach the table / View mapping screen, select the Transform button, find your field, and change the Type to SmallDateTime.
Good Luck!
Michael Lee
November 22, 2004 at 1:19 pm
I've had trouble with moving data over in the past. The problems I ran into were bad data. Access allowed some dates that SQL does not accept. I had a one row that had a year of 1800-something which was causing the DTS import to error. There was also a couple of incomplete dates in my Access database.
The point is, make sure all your data is good. My experience with it was great once I got rid of the bad dates in the Access DB.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply