Post reply

Datetime to Smalldatetime

  • July 3, 2012 at 5:32 am

    #258292

    Hi there,

    We have a legacy system in which the data type of particular column is smalldatetime. We are currently in the process of transferring data from Sybase SQL Anywhere to SQL Server db.

    we got an error when we tried to insert datetime column's values into smalldatetime filed. is there any workaround to convert datetime to Smalldatetime??

    thanks in advance:cool:

  • July 3, 2012 at 5:36 am

    #1508168

    What is your process of transfering from Sybase to SQL.

    I would make use of the datetime2 data type and load into a staging table, then use a convert call to convert from datetime2 to smalldatetime?

    In an old position the main DB was on SQL Anywhere and dates where converted to varchar(25) in SQL 2000 or Datetime2 in SQL 2008, due to the fact it used ODBC formats and added an extra couple of points in the milliseconds part of the datetime which cause conversion issues.

  • July 3, 2012 at 6:08 am

    #1508183

    SS Developer (7/3/2012)

    Hi there,

    We have a legacy system in which the data type of particular column is smalldatetime. We are currently in the process of transferring data from Sybase SQL Anywhere to SQL Server db.

    we got an error when we tried to insert datetime column's values into smalldatetime filed. is there any workaround to convert datetime to Smalldatetime??

    thanks in advance:cool:

    The obvious answer is that you have some datetime data that can't be converted and it's probably because the datetime is outside the range of smalldatetime. You're simply going to have to write a query that prevalidates the dates and find the errors.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 3, 2012 at 6:17 am

    #1508190

    Another one to watch out for is Sybase's ability to store the following date

    0000/00/00 00:00:00.00000

    Even if you try to convert this to datetime2 you will get an error as that is outside the scope of datetime2.

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy