Error converting data type varchar to bigint

  • I have a scenario wherein I have to combine five date columns into one . Its SQL Server 2005

    UPDATE dbo.LOTTERY_CAL_INV_LOAD

    SET

    LOTTERY_CAL_INV_LOAD.Status_Id =

    Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Pack_Id,112)+'

    '+Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Invoice_Id,112)+'

    '+Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Issue_Date,112)+'

    '+Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Receive_Date,112)+'

    '+Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Active_Date,112)+'

    '+Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Settle_Date,112)

    when i try to run the above query i am getting error

    Msg 8114, Level 16, State 5, Line 1

    Error converting data type varchar to bigint.

    How to solve this issue. i want all the columns data to me merged and inserted into single column

  • It means one or more rows are returning something that can't be converted into a big integer.

    You might be able to use IsNumeric to find which rows.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • i am converting the date fileds and concatinating them. as i had converted to varchar i am getting error while i am trying to update the staus_id(bigint) column with this concatinated(vachar) value

  • jagadeeps (11/17/2010)


    i am converting the date fileds and concatinating them. as i had converted to varchar i am getting error while i am trying to update the staus_id(bigint) column with this concatinated(vachar) value

    Exactly. And one or more of the rows can't convert.

    Try this, see what you get:

    select * from dbo.LOTTERY_CAL_INV_LOAD

    where IsNumeric(Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Pack_Id,112)

    +Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Invoice_Id,112)

    +Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Issue_Date,112)

    +Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Receive_Date,112)

    +Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Active_Date,112)

    +Convert(Varchar(50),LOTTERY_CAL_INV_LOAD.Settle_Date,112)) = 0;

    I also noticed, you have hard-coded line-breaks into your concatenation. How is a value with line-breaks in it supposed to go in a numeric column?

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

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