Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    7

    Unanswered: How to resolve this error--Arithmetic overflow varchar -> smalldatetime

    sybase ase:

    insert into dbo.REF_SCOV2_SPECIAL_COST(
    SUB_CLASS ,
    PLAN_TYPE ,
    PLAN_CLASS ,
    LINE_CODE ,
    REV ,
    START_DATE ,
    END_DATE )
    select SUB_CLASS ,
    PLAN_TYPE ,
    PLAN_CLASS ,
    LINE_CODE ,
    round(rand(),2) ,
    convert(smalldatetime ,'20080101',105),
    convert(smalldatetime ,'29990101',105)
    from DRV_SERVICE_T



    Server Message: Number 3606, Severity 10
    Server 'DSS1_PPRD', Line 1:
    Arithmetic overflow occurred.
    Server Message: Number 247, Severity 16
    Server 'DSS1_PPRD', Line 1:
    Arithmetic overflow during explicit conversion of VARCHAR value '29990101' to a SMALLDATETIME field .

  2. #2
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    Hi,

    In general, Arithmetic Overflow occurs, when we try to insert data which is bigger that its range determined.

    As per Sybase, below is the range for Date/time datatypes.

    smalldatetime(4 bytes) - January 1, 1900 to June 6, 2079
    datetime(8bytes) - January 1, 1753 to December 31,9999
    date(4bytes) - January 1, 0001 to December 31,9999


    You can use datetime or date datatypes as per your requirement for typecasting. Both will work.

    1> select convert(date,'29990101',105)
    2> select convert(datetime,'29990101',105)
    3> go

    -------------------
    Jan 1 2999 12:00AM

    (1 row affected)

    -------------------
    Jan 1 2999 12:00AM

    (1 row affected)
    1>

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    use datetime instead
    The range for smalldatetime is January 1, 1900 to June 6, 2079

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •