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 '10 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:
Please try to add the correct dateformat to the top of your query, and I hope it works:
If the dateformat is not an issue then please try to look for some out of range values in your Access table. The range of the smalldatetime in SQL Sever is January 1, 1900, through June 6, 2079, so if one of the records in Access has value outside of this range it won't fit into smalldatetime.
answered Jun 23 '10 at 08:15 PM