November 17, 2010 at 1:28 pm
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
November 17, 2010 at 1:31 pm
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
November 17, 2010 at 1:44 pm
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
November 17, 2010 at 1:48 pm
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