x

convert ms-access datetime to sql server datetime

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.

more ▼

asked Jun 23, 2010 at 03:43 AM in Default

user-2049 (google) gravatar image

user-2049 (google)
1 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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"

more ▼

answered Jun 23, 2010 at 03:50 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

+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, 2010 at 08:38 PM Oleg
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jun 23, 2010 at 08:15 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x29

asked: Jun 23, 2010 at 03:43 AM

Seen: 4633 times

Last Updated: Jun 23, 2010 at 03:43 AM