将数据类型varchar转换为数字时出错 [英] Error converting data type varchar to numeric
本文介绍了将数据类型varchar转换为数字时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有下表，我正在尝试插入值，但它给了我错误
I have the below table and I am trying to insert the values but it is giving me the error
[Check_In](
[Room_Account] [varchar](50) NULL,
[Group_Account] [varchar](50) NULL,
[Type_Id] [varchar](50) NULL,
[Room_No] [int] NULL,
[Guest_Id] [varchar](50) NULL,
[Guest_Name] [varchar](250) NULL,
[Group_Id] [int] NULL,
[Checkin_Type] [varchar](50) NULL,
[Reservation_No] [int] NULL,
[Contact_Name] [varchar](250) NULL,
[Arrival_Date] [datetime] NULL,
[Departure_Date] [datetime] NULL,
[No_Of_Days] [int] NULL,
[No_Of_Adults] [int] NULL,
[No_Of_Childs] [int] NULL,
[Arrival_source] [varchar](50) NULL,
[Company_id] [varchar](500) NULL,
[Special_Request] [varchar](1000) NULL,
[Billing_Instruction] [varchar](1000) NULL,
[Payment_Type] [varchar](250) NULL,
[Card_Type] [varchar](250) NULL,
[Card_No] [varchar](250) NULL,
[Holder_Name] [varchar](250) NULL,
[Expiry_Date] [datetime] NULL,
[Bank_Name] [varchar](250) NULL,
[Bank_SlipNo] [varchar](250) NULL,
[Rate_Code] [varchar](500) NULL,
[Amount] [money] NULL,
[Deposit_Amount] [money] NULL,
[ResvSource_Id] [varchar](250) NULL,
[Market_Segment] [varchar](250) NULL,
[CheckIn_Status] [varchar](250) NULL,
[User_Shift] [varchar](50) NULL,
[User_Name] [varchar](250) NULL,
[Arrival_time] [varchar](50) NULL,
[GuestNameReal] [varchar](1000) NULL,
[Guest_Title] [varchar](50) NULL,
[Deposit_Required] [money] NULL,
[Remarks] [varchar](3000) NULL,
[Group_Leader] [varchar](50) NULL,
[Type_ratecode] [varchar](50) NULL,
[Bill_pref] [varchar](50) NULL,
[Credit_Limit] [decimal](18, 2) NULL,
[ActualCredit_Limit] [money] NULL,
[grpcode] [varchar](250) NULL,
[weekdayrateonly] [int] NULL,
[weekendamount] [money] NULL,
我尝试过：
插入以下查询
What I have tried:
inserting the below query
insert into check_in(room_account,group_account,type_id,room_no,guest_id,guest_name,group_id,checkin_type,reservation_no,contact_name,arrival_date,departure_date,no_of_days,no_of_adults,no_of_childs,company_id,special_request,billing_instruction,payment_type,Card_type,Card_no,expiry_date,bank_name,bank_slipno,rate_code,amount,deposit_amount,resvsource_id,market_segment,checkin_status,user_shift,user_name,folio_count,Checkin_Form,Arrival_Time,GuestNameReal,Guest_Title,Deposit_Required,Remarks,Group_Leader,type_ratecode,bill_pref,credit_limit,actualcredit_limit,weekendamount,weekdayrateonly)values('2506201817043086-1','','STUDIOT','1704','12794','NOOR SHAWAL BIN NASRI','','Individual','1434','',cast('25-Jun-2018'as smalldatetime),cast('27-Jun-2018'as smalldatetime),'2','2','0','','','','CreditCard','VISA CARD','4460 3100 3037 3359',cast('01-Jul-2020'as smalldatetime),'','','PROMOTION 158',cast('158'as money),cast('0.0000'as money),'PHONE','PROMOTION','CheckIn','1','sa',3086,'CheckIn','10:09:48 AM','','', cast('316'as money),'' ,'','RTCode','','','300','cast('158' as money),'0')
我试图解决但我没有得到问题的地方
有人可以请帮助我
I have tried to resolve but I am not getting where the problem is
can someone please help me
推荐答案
将您的字段与您输入的数据相匹配：
Match your fields up with the data you are entering:
room_account '2506201817043086-1'
group_account ''
type_id 'STUDIOT'
room_no '1704'
guest_id '12794'
guest_name 'NOOR SHAWAL BIN NASRI'
group_id ''
checkin_type 'Individual'
reservation_no '1434'
contact_name ''
arrival_date cast('25-Jun-2018'as smalldatetime)
departure_date cast('27-Jun-2018'as smalldatetime)
no_of_days '2'
no_of_adults '2'
no_of_childs '0'
company_id ''
special_request ''
billing_instruction ''
payment_type 'CreditCard'
Card_type 'VISA CARD'
Card_no '4460 3100 3037 3359'
expiry_date cast('01-Jul-2020'as smalldatetime)
bank_name ''
bank_slipno ''
rate_code 'PROMOTION 158'
amount cast('158'as money)
deposit_amount cast('0.0000'as money)
resvsource_id 'PHONE'
market_segment 'PROMOTION'
checkin_status 'CheckIn'
user_shift '1'
user_name 'sa'
folio_count 3086
Checkin_Form 'CheckIn'
Arrival_Time '10:09:48 AM'
GuestNameReal ''
Guest_Title ''
Deposit_Required cast('316'as money)
Remarks ''
Group_Leader ''
type_ratecode 'RTCode'
bill_pref ''
credit_limit ''
actualcredit_limit '300'
weekendamount 'cast('158' as money)
weekdayrateonly '0'然后查看未存储为VARCHAR的列：
And then look at the columns that are not stored as VARCHAR:
[Room_No] [int] NULL,
[Group_Id] [int] NULL,
[Reservation_No] [int] NULL,
[Arrival_Date] [datetime] NULL,
[Departure_Date] [datetime] NULL,
[No_Of_Days] [int] NULL,
[No_Of_Adults] [int] NULL,
[No_Of_Childs] [int] NULL,
[Expiry_Date] [datetime] NULL,
[Amount] [money] NULL,
[Deposit_Amount] [money] NULL,
[Deposit_Required] [money] NULL,
[Credit_Limit] [decimal](18, 2) NULL,
[ActualCredit_Limit] [money] NULL,
[weekdayrateonly] [int] NULL,
[weekendamount] [money] NULL,然后看看你输入的内容：
Then look at what you are putting in them:
[Room_No] '1704'
[Group_Id] ''
[Reservation_No] '1434'
[Arrival_Date] cast('25-Jun-2018'as smalldatetime)
[Departure_Date] cast('27-Jun-2018'as smalldatetime)
[No_Of_Days] '2'
[No_Of_Adults] '2'
[No_Of_Childs] '0'
[Expiry_Date] cast('01-Jul-2020'as smalldatetime)
[Amount] cast('158'as money)
[Deposit_Amount] cast('0.0000'as money)
[Deposit_Required] cast('316'as money)
[Credit_Limit] ''
[ActualCredit_Limit] '300'
[weekendamount] 'cast('158' as money)
[weekdayrateonly] '0'所有这些看起来都是合适的数据，除了空值：
All of those look like appropriate data, except the "empty" values:
[Group_Id] ''
[Credit_Limit] ''因此，请尝试将它们显式设置为NULL并查看是否有帮助：
So try explicitly setting them to NULL and see if that helps:
insert into check_in(room_account,group_account,type_id,room_no,guest_id,guest_name,group_id,checkin_type,reservation_no,contact_name,arrival_date,departure_date,no_of_days,no_of_adults,no_of_childs,company_id,special_request,billing_instruction,payment_type,Card_type,Card_no,expiry_date,bank_name,bank_slipno,rate_code,amount,deposit_amount,resvsource_id,market_segment,checkin_status,user_shift,user_name,folio_count,Checkin_Form,Arrival_Time,GuestNameReal,Guest_Title,Deposit_Required,Remarks,Group_Leader,type_ratecode,bill_pref,credit_limit,actualcredit_limit,weekendamount,weekdayrateonly)values('2506201817043086-1','','STUDIOT','1704','12794','NOOR SHAWAL BIN NASRI',NULL,'Individual','1434','',cast('25-Jun-2018'as smalldatetime),cast('27-Jun-2018'as smalldatetime),'2','2','0','','','','CreditCard','VISA CARD','4460 3100 3037 3359',cast('01-Jul-2020'as smalldatetime),'','','PROMOTION 158',cast('158'as money),cast('0.0000'as money),'PHONE','PROMOTION','CheckIn','1','sa',3086,'CheckIn','10:09:48 AM','','', cast('316'as money),'' ,'','RTCode','',NULL,'300','cast('158' as money),'0')
但请帮个忙：不要在数字数据周围加上引号！
But do yourself a favour: don't put quotes around numeric data!
这篇关于将数据类型varchar转换为数字时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
