| Author |
Topic |
|
Jonny1409
Posting Yak Master
133 Posts |
 Posted - 2007-09-04 : 05:37:56
|
| Hello,I'm trying to import some data from a table in an Access Database into my SQL Server.It's one field I'm having a problem with and I don't know how to get round it.Basically the field in Access is called "DueToStart" and is a "Date/Time" type field.It shows values as "09:00:00" etc.When I try to import this table into SQL, I get the following message, and I don't know how to get round it."Error as destination for row number 1. Errors encountered so far in this task: 1.Insert Error, column 3('DueToStart', DBTYPE_DBTIMESTAMP), status 6: Data overflow.Invalid character value for cast specification." |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 05:47:08
|
Do the values in Access have a date, or just a time?Are the dates outside the valid range for SQL Server? (I don't know what the valid range is for Access, maybe it is broader than SQL Server?)From Books Online:quote:
datetime - Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.smalldatetime - Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
Kristen |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-04 : 05:48:30
|
Access just have DATETIME, denoted DATE.BUT! In Access, the value "09:00:00" is interpreted as "1899-12-30 09:00:00" in SQL Server, which breaks the date limit for smalldatetime.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-09-04 : 05:56:14
|
| So do you have any ideas how I can get round this ?Or am I completely screwed ? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-04 : 06:03:59
|
You could try to add DATEADD("d", 2, DueDate) when importing.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 07:14:04
|
| Are we sure the OP is trying to import into a SMALLdatetime column, rather than a DATETIME one? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 07:35:01
|
| "I think SQL Server does this automagically"Ha! Hopefully only for a newly-created table, and not for a pre-existing table with a DATETIME column ...If the OP is creating a new table then the definition of the table can be changed in DTS so that it creates a DATETIME column instead.Kristen |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-04 : 07:41:02
|
True enough.OP didn't state if he is trying to import to an existing table or creating a new table for this import.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-09-04 : 07:44:32
|
| Sorry - I'm trying to import into a pre-existing table where the field is smalldatetime |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-09-04 : 07:51:48
|
Then change the script to include a DATEADD("d", 2, SourceCol) so that you shift the base date from "18991230" to "19000101" instead.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 08:11:26
|
| Might need casting even then?DATEADD("d", 2, CONVERT(datetime, SourceCol))Kristen |
|
|
|
|
Import of Data from Access DB
See