CODEWITHSUNDEEP
sqlsql-serverselectsql-server-2008-r2
Alex N
Alex N

Reputation: 1121

Arithmetic overflow error converting expression to data type int - function cast not work

Good day.

I know that this question very popular, but i don't see help for me...

Structure database:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ExPRoG_Statistic_banners](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [user_id] [varchar](50) NULL,
    [banner_idn] [varchar](50) NULL,
    [banner_page] [varchar](150) NULL,
    [action] [varchar](50) NULL,
    [datetime] [datetime] NULL,
    [server_referer] [varchar](150) NULL,
    [ip] [varchar](50) NULL,
    [browser] [varchar](500) NULL,
    [id_firm] [varchar](50) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

SQL:

SELECT *
FROM (
    SELECT 
          id
        , [user_id]
        , [day] = DAY(datetime)
    FROM Table
    WHERE banner_idn='50927853' AND datetime BETWEEN '2013-08-08 00:00:000' AND '2013-08-22 00:00:000'
) src
PIVOT 
(
    SUM(cast(id as bigint)) FOR [day] IN (
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
        [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
        [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) pvt

i make select, but i have error:

Arithmetic overflow error converting expression to data type int.

Type input id - int.

For remove error i want use cast() - cast(id as bigint), but now i get error Incorrect syntax near '('. for line cast(id as bigint)

Tell me please where error?

Tell me please how remove error?

Upvotes: 2

Views: 8395

Answers (1)

Devart
Devart

Reputation: 122002

Try this one -

SELECT *
FROM (
    SELECT 
          id
        , [user_id]
        , [day] = DAY([datetime])
    FROM dbo.[Table]
    WHERE banner_idn = '50927853' 
        AND [datetime] BETWEEN '20130808' AND '20130822'
) src
PIVOT 
(
    COUNT(id) FOR [day] IN (
        [1], [2], [3], [4], [5], [6], [7], [8], [9], [10],
        [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
        [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) pvt

Upvotes: 1

Related Questions