将数据类型varchar转换为bigint时出错.MSSQL2008

本文关键字：出错 MSSQL2008 bigint 数据类型 varchar 转换 | 更新日期: 2023-09-27 18:25:30

运行此SQL时出现此错误。

我的错误信息是：

Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to bigint.

    SELECT k.hit, 
           k.reach, 
           CONVERT(VARCHAR(20), j.signon_date,101) AS dispatch_date, 
           i.name AS agency_name, 
           i.name, 
           a.id, 
           a.pps_id, 
           a.brand_id, 
           CONVERT(VARCHAR(20), a.[date],101) AS [date], 
           b.id AS pome_route_detail_id, 
           b.route_id, 
           z.outlet_code as barangay_id, 
           b.reach, 
           l.name AS region_name, 
           e.name AS province_name, 
           d.name AS municipal_name, 
           z.outlet_name AS barangay_name, 
           c.[population], 
           c.household, 
           c._ph_id, 
           z.lattitude AS latitude, 
           z.longtitude AS longitude, 
           f.name AS team_name, 
           f.teamCode, 
           g.name AS brand_name, 
           CASE j.[status] 
               WHEN 1 THEN 'ACTIVE' 
               WHEN 2 THEN 'CLOSED' 
               WHEN 3 THEN 'BASE PREPARATION' 
               WHEN 4 THEN 'CANCELLED' 
               ELSE 'NOT REPORTED' 
           END AS dispatch_status, 
           a.[date] AS formatted_date,CONVERT(VARCHAR(20), 
           k.[start_date],101) AS start_date
           from dbo.pome_route_transaction_detail AS k   

        INNER join dbo.pome_route_transaction AS j ON j.id = k.pome_route_transaction_id 
       and j.status !=4 and k.status = 1    
        INNER join dbo.pome_route AS a on a.id = j.route_id 
        left join dbo.pome_route_details AS b ON a.id = b.route_id and b.[hospital_id] = k.     [pome_hospital_id] 
 INNER JOIN dbo.outlets AS z ON z.outlet_code = k.pome_hospital_id          
               INNER JOIN dbo.barangay AS c ON c.id = z.barangay_id    
               INNER JOIN dbo.municipal AS d ON d.id = c.municipal_id   
               INNER JOIN dbo.province AS e ON e.id = d.province_id   
               INNER JOIN dbo.region AS l ON l.id = e.region_id   
               INNER JOIN dbo.team AS f ON f.id = a.pps_id   
               INNER JOIN dbo.brand AS g ON g.id = a.brand_id   
               INNER JOIN dbo.agency_team AS h ON h.team_id = f.id             
               INNER JOIN dbo.agency AS   i    ON i.id = h.agency_id

我包括from子句和联接。Where条款，Where a.[日期]='07/25/2013'谢谢你们的帮助！我把所有的代码都放在这里，呵呵

将数据类型varchar转换为bigint时出错.MSSQL2008

select中的代码看起来不错。基本上，你需要检查你的每一个条件——每个where和每个join。我们不能为您回答这个问题，因为我们没有桌子。例如：您的where是：

Where a.[date] = '07/25/2013'

现在，如果a.[date]实际上是unix时间（epoch中的毫秒）的bigint，那么肯定会失败。

纯粹基于命名，我的最佳猜测是：

ON z.outlet_code = k.pome_hospital_id

在所有其他方面，我猜（但是：您需要检查）每个*_id都是int或bigint，所以没有问题。然而，outlet_code听起来可能是varchar。事实上，如果它有像'P20'这样的值，那么如果pome_hospital_id是bigint，那就永远不会起作用。

相关文章：