March 3, 2003 at 8:56 pm
Hi,
I'm trying to convert an Access Database to an SQL server Databases. In one of the tables in Access I have a column with Date/Time as Datatype and Medium Date as the Data (e.g. 9:00 AM). But when I try to import this Table in SQL Server I get an error that "Invalid character value for cast expression". Please advise on what might be the problem.
March 6, 2003 at 8:00 am
This was removed by the editor as SPAM
March 6, 2003 at 9:20 am
Seems like it would work. Access works pretty much like sql, date is stored to the left of the decimal, time to the right, formatting has nothing to do with storage - well, in SQL anyway. Sure you don't have some other junk char that is causing the issue? Workaround might be to load into a varchar col, then convert using TSQL.
Andy
March 7, 2003 at 4:29 am
Andy, I think formatting is an issue and it depends on what you are using to do the transfer. DTS for example will use select to get data and the date/time will be formatted as date/time not how it is stored. I have had trouble like this before (not specifically with access) where either the date retrieved has an invalid year (eg 0203-01-01 is valid in some systems but in sql) or the local settings for date format is incompatable (eg UK dd/mm/yyyy and US mm/dd/yyyy).
Edited by - davidburrows on 03/07/2003 04:29:50 AM
Far away is close at hand in the images of elsewhere.
Anon.
March 7, 2003 at 8:00 am
Just to clarify.
You state that Access is set to medium date but you give a medium time as an example.
"Medium Date as the Data (e.g. 9:00 AM). "
You will have a problem if you are only storing medium times in the Access table and trying to insert it into a datetime field in sql. Sql will require a date value which is missing. You would either have to append a date to the time value or use varchar.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply