convert ms-access datetime to sql server datetime

Question

ms-access table structure like this(here EFFECTIVE_TIMESTAMP= date/time data type)

EFFECTIVE_TIMESTAMP
1/24/2008 1/24/2008 7:00:00 AM 1/24/2008 5:00:00 AM 1/24/2008 12:00:00 PM

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.

Answer 1

0

EDIT

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.

END EDIT

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"

answered Jun 23 '10 at 03:50 AM

Magnus Ahlkvist
13.7k ● 13 ● 17 ● 30

+1 as the string format 'YYYYMMDD hh:mm:ss.nnn' that you mention is the one to use if the string format has to be used ('YYYYMMDD hh:mm:ss' is just as good for a smalldatetime). I cannot remember for sure but believe that format(the_date, "YYYYMMDD HH:MM:SS") should do the trick, so select [EFFECTIVE_TIMESTAMP] part of the query in question can be changed to select format([EFFECTIVE_TIMESTAMP],"YYYYMMDD HH:MM:SS") as the_stamp. I believe that openrowset queries are pass-through, so they will take the syntax specific to the source.

Jun 23 '10 at 08:38 PM Oleg

(comments are locked)

Answer 2

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:

set dateformat 'dmy';
select cast('1/24/2008 7:00:00 AM' as smalldatetime) test;

This results in the following message:

Msg 296, Level 16, State 3, Line 2
The conversion of char data type to smalldatetime data 
type resulted in an out-of-range smalldatetime value.

set dateformat 'mdy';
select cast('1/24/2008 7:00:00 AM' as smalldatetime) test;

This runs just fine:

test

2008-01-24 07:00:00

Please try to add the correct dateformat to the top of your query, and I hope it works:

set dateformat 'mdy';
insert into [BIAS]([EFFECTIVE_TIMESTAMP])
select * 
    from openrowset
    (
        'Microsoft.Jet.OLEDB.4.0','c:\SYSTEM2DB.mdb';'Admin';, 
        'select [EFFECTIVE_TIMESTAMP] from [BIAS]'
    ) as bias;
go

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.

Oleg

convert ms-access datetime to sql server datetime

Follow this question

Related Questions