错误转换数据类型varchar到bigint。MSSQL2008

I have this error when I run this SQL.

当我运行这个SQL时，我有这个错误。

MY ERROR message is :

我的错误信息是:

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

I included the from clause and the joins. The Where Clause is, Where a.[date] = '07/25/2013' Thanks for the help guys! i put all the codes here hehehe

我包括from子句和join。Where子句是a。[date] = '07/25/2013'谢谢你们的帮助!我把所有的代码都写在这里了。

1 个解决方案

#1

The code in the select looks ok-ish. Basically, you need to check each and every one of your conditions - every where and every join. We can't answer this for you, because we don't have the table. For example: your where is:

select中的代码看起来很好。基本上，你需要检查你的每一个条件——每一个地方和每个连接。我们不能为你回答这个问题，因为我们没有桌子。例如:你在哪里:

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

now, if a.[date] is actually a bigint of the unix-time (milliseconds into epoch), then sure: this would fail.

现在,如果一个。[日期]实际上是一个统一时间的bigint(以毫秒为单位)，然后确定:这将失败。

My best guess based purely on naming, would be this one:

我最好的猜测完全基于命名，将会是这个:

ON z.outlet_code = k.pome_hospital_id

on all the rest, I'm guessing (but: you need to check) that each *_id is an int or bigint, so aren't the problem. However, outlet_code sounds like something that could be a varchar. And indeed, if it has values like 'P20' then that won't ever work if pome_hospital_id is a bigint.

在所有的rest中，我猜测(但是:您需要检查)每个*_id是int或bigint，所以不是问题。然而，outlet_code听起来像是一个varchar。实际上，如果它有像P20那样的值，那么如果它是一个bigint，那么它就永远不会起作用。

#1

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