ms-access table structure like this(here EFFECTIVE_TIMESTAMP= date/time data type)
IN SQL SERVER EFFECTIVE_TIMESTAMP =SMALLDATETIME
iam written query from access to sql server insert into [BIAS] ([EFFECTIVE_TIMESTAMP]) ' select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','c:\SYSTEM2DB.mdb';'Admin';, 'select [EFFECTIVE_TIMESTAMP] from [BIAS]') AS bias
ERROR OCCURS The conversion from datetime data type to smalldatetime data type resulted in a smalldatetime overflow error.
asked Jun 23, 2010 at 03:43 AM in Default
I checked what a Date/Time-datatype in Access translates to using OPENROWSET, and it does translate to a DateTime-datatype (I did a SELECT * INTO... using OPENROWSET and then checked the datatype of the columns in the created table).
So I'm wondering if the column you have in Access is really of Date/Time datatype? If that's the case, I'd say you should check if you have values in the column which won't fit into a smalldatetime in SQL Server, like Oleg suggests.
I always try to convert a datetime to the string format "YYYYMMDD hh:mm:ss.nnn" when I'm moving dates between systems. That way, I'm always sure the date conversion will work, regardless of my regional settings.
I no longer remember MS Access syntax for DatePart-function, but that's probably what you want to look into, so that you can convert your dates to something like "20080124 07:00:00.000"
It appears that your problem is caused by the dateformat mismatch. Here are the snippets which both use one of the values you supplied in your question:
This results in the following message:
This runs just fine: