Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
malletts
Starting Member
16 Posts |
 Posted - 2004-03-15 : 16:37:30
|
| Hi all,I'm importing some data from Access into SQL server 7.0. When trying to import datetime fields from Access, I'm getting errors ' Invalid character for cast specification' on datetime fields which just have times in them i.e. 09:00. There doesn't seem to be any problems with these fields which just have the date portion of datetime in themAll help greatly appreciated.Stephen |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-03-15 : 16:50:31
|
| Well, what should the date be when the date is missing? SQL Server does not have a time data type, only datetime. If you don't specify the time portion, SQL Server will assume 00:00. But you must specify a date. If you don't have one, use Jan 1 1900.Here is what I would recommend doing: Create a staging table. For that column, use VARCHAR. Then import the data into this staging table. Now move the data from the staging table to the destination table using T-SQL. We can help with the T-SQL part but we need to know what date you'll use.Tara |
 |
|
|
malletts
Starting Member
16 Posts |
Posted - 2004-03-19 : 13:04:49
|
| Hi,thx for your reply. In 2 of the systems I am migrating, I am able to manually capture the 'rejected records' i.e dates before 1900 due to relatively small volume of records and adjust these accordingly. For the more troublesome application which has a high volume of dates/times I have been able to use Upsizing Wizard which has thankfully worked ok. Bit of a roundabout way of doing it but seems to work ok.Thanks,Stephen |
|
|
|
|
|
|
Importing date/time from Access