import table with date time value from ms access to sql server

  • my sql server defalut date time format is small. but I want to import one table from ms access with the date time info into sql server. it give me over float error. Thanks.

  • You don't say exactly how you are doing it.

    We seem to have horrendous problems because we don't use US as the default format so some of this may be applicable. There's also the question of times as well.

    If you are doing it in access, look at using something like cdate to make sure it gets a date or even format the dates as yyyymmdd to make it easier.

    If you are using DTS look at using a transform to do the conversion. Use a query and format the dates as you want them. In DTS, look at the date time transfer and sort it out from there. Again, you seem to be stuck with yyy-MM-dd but ther's nothing wrong with that.

  • This should work. Make a simple test:

    Create table DateTest (OnlyDate datetime)

    GO

    select * from datetest

    insert into DateTest (onlydate)

    Select '20060102' -- Format your date as YYYYMMDD as mentioned before

    select * from datetest

    Are U using ODBC?

  • alter the table that was created for the import by changing the smalldatetime field to datetime.  Then import your data again but make sure you do not drop the table first but you may want to clear the previous records out first.  This has happened to me many times before and using the above is how i get round it.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply