Post reply

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

  • October 2, 2006 at 9:20 am

    #68955

    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.

  • October 3, 2006 at 1:15 am

    #663782

    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.

  • October 3, 2006 at 7:22 am

    #663819

    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?

  • October 5, 2006 at 2:12 am

    #664242

    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