Conversion failed when converting the varchar value '201ex' to data type int.

Question

Conversion failed when converting the varchar value '201ex' to data type int.

imneaz

Say Hey Kid

Points: 711
More actions
July 18, 2016 at 10:49 pm

#320035

Dear Expert ,
I have a problem ...please help me out..
Conversion failed when converting the varchar value '201ex' to data type int.
SELECT
LTRIM(RTRIM(CONVERT(varchar(100),Req.TID)))AS TransactionID,'GakkMedia' AS PartnerName,LTRIM(RTRIM(Req.MSISDN)) AS MSISDN ,
LTRIM(RTRIM(VC.ORIGIN_PORT)) AS [ShortCode], 'PushpullContent' AS ContentName, LTRIM(RTRIM(Req.TranId)) AS ContentID, '' AS ServiceID,
LTRIM(RTRIM(Req.Code)) AS [Subscription group id], LTRIM(RTRIM(Req.Code)) AS [Charging code], '' AS ContentType, LTRIM(RTRIM(Req.Amount)) AS ContentPrice, LTRIM(RTRIM(KMM.CHANNEL_TYPE)) AS [Channel Type],
'Subscription' AS ChargeType, LTRIM(RTRIM(Req.Code)) AS ChargingReference, LTRIM(RTRIM(Req.Amount)) AS RequestedAmount,
'debit' AS TransactionType, CASE Req.Status WHEN '100' THEN 'Success' ELSE 'FAILED' END AS TransactionStatus,
Req.Status AS ResultCode ,
RIGHT('00' + CONVERT(varchar(2), DATEPART(month, Res.Request_Time)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(day, Res.Request_Time)), 2) +
RIGHT('0000' + CONVERT(varchar(4), DATEPART(year, Res.Request_Time)), 4) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(HOUR, Res.Request_Time)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(MINUTE, Res.Request_Time)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(SECOND, Res.Request_Time)), 2) AS RequestTimestamp,
RIGHT('00' + CONVERT(varchar(2), DATEPART(month, Res.Resonse_Time)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(day, Res.Resonse_Time)), 2) +
RIGHT('0000' + CONVERT(varchar(4), DATEPART(year, Res.Resonse_Time)), 4) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(HOUR, Res.Resonse_Time)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(MINUTE, Res.Resonse_Time)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(SECOND, Res.Resonse_Time)), 2) AS ResponseTimestamp,
RIGHT('00' + CONVERT(varchar(2), DATEPART(month, GETDATE() + 1)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(day, GETDATE() + 1)), 2) +
RIGHT('0000' + CONVERT(varchar(4), DATEPART(year, GETDATE() + 1)), 4) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(HOUR, GETDATE() + 1)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(MINUTE, GETDATE() + 1)), 2) +
RIGHT('00' + CONVERT(varchar(2), DATEPART(SECOND, GETDATE() + 1)), 2) AS NextChargingDate,
CONVERT(VARCHAR(10), 'Subscribe') AS SubscriptionStatus,
'' AS TotalConsumedUnit, LTRIM(RTRIM(Res.TID)) AS [Transaction ID of SDP]
INTO #tmp2
FROM Banglalink.dbo.tbl_Key_Map_Master AS KMM JOIN Banglalink.dbo.tbl_VAS_Charge AS VC ON KMM.CHARGE_ID = VC.CHARGE_ID
JOIN dbo.tbl_Outbox_Charging_07 AS Req ON Req.ServiceId = KMM.SERVICE_ID
LEFT JOIN dbo.tbl_Outbox_Response_07 AS Res ON Res.Out_Tranid = Req.TranId AND Req.ServiceId =KMM.SERVICE_ID
WHERE Req.Status <> 100 AND CAST(Req.Processtime as DATE)= '2016-07-18'
SELECT * FROM #tmp2
-------------------------------------- CREATE TABLE ----------------------------
USE [Banglalink]
GO
/****** Object: Table [dbo].[tbl_Key_Map_Master] Script Date: 07/19/2016 10:47:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Key_Map_Master](
[SERVICE_CODE] [varchar](36) NOT NULL,
[SERVICE_ID] [int] NOT NULL,
[KEYWORD] [varchar](20) NOT NULL,
[ACTUAL] AS (replace([KEYWORD],' ','')),
[SHORT_CODE] [varchar](15) NOT NULL,
[SERVICE_NAME] [varchar](100) NOT NULL,
[CATEGORY] [varchar](100) NULL,
[DESCRIPTION] [varchar](500) NULL,
[CONTENT_PREFIX] AS (case [CONTENT_TYPE] when (1) then 'MonoTone' when (2) then 'Logo' when (3) then 'GPRS' when (4) then 'Text' when (9) then 'Mix' else 'x' end),
[CONTENT_TYPE] [int] NULL,
[CONTENT_TYPE_CP] [int] NULL,
[REPLY_PREFIX] AS (case [REPLY_TYPE] when (1) then 'Push-Pull' when (2) then 'Subscription' else 'Other' end),
[REPLY_TYPE] [int] NULL,
[CHARGE_ID] [int] NOT NULL,
[SP_NAME] [varchar](50) NULL,
[KEYWORD_ASSIGN] [varchar](1) NULL,
[EXPIRED] [varchar](1) NOT NULL,
[TIME_STAMP] [datetime] NOT NULL,
[LAST_UPDATE] [datetime] NULL,
[CHANNEL_TYPE] [varchar](5) NULL,
CONSTRAINT [PK_tbl_Key_Map_Master] PRIMARY KEY CLUSTERED
(
[SERVICE_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_tbl_Key_Map_Master] UNIQUE NONCLUSTERED
(
[SERVICE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_tbl_Key_Map_Master_1] UNIQUE NONCLUSTERED
(
[SERVICE_NAME] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 = MonoTone, 2 = Logo, 3 = WAP-Push, 4 = Text' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbl_Key_Map_Master', @level2type=N'COLUMN',@level2name=N'CONTENT_TYPE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'2 = MonoTone, 3 = Logo, 0 = Text' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbl_Key_Map_Master', @level2type=N'COLUMN',@level2name=N'CONTENT_TYPE_CP'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 = Push-Pull, 2 = Subscription' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbl_Key_Map_Master', @level2type=N'COLUMN',@level2name=N'REPLY_PREFIX'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'1 = Push-Pull, 2 = Subscription' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tbl_Key_Map_Master', @level2type=N'COLUMN',@level2name=N'REPLY_TYPE'
GO
USE [Banglalink]
GO
/****** Object: Table [dbo].[tbl_VAS_Charge] Script Date: 07/19/2016 10:47:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_VAS_Charge](
[RECORD_NO] [varchar](36) NOT NULL,
[CHARGE_ID] [int] NOT NULL,
[CHARGE_PORT] [varchar](9) NOT NULL,
[ORIGIN_PORT] [varchar](5) NOT NULL,
[DESCRIPTION] [varchar](100) NULL,
[EXPIRED] [varchar](1) NOT NULL,
[TIME_STAMP] [datetime] NOT NULL,
CONSTRAINT [PK_tbl_VAS_Charge] PRIMARY KEY CLUSTERED
(
[RECORD_NO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY],
CONSTRAINT [IX_tbl_VAS_Charge] UNIQUE NONCLUSTERED
(
[CHARGE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Key_Map_Master] WITH NOCHECK ADD CONSTRAINT [FK_tbl_Key_Map_Master_tbl_VAS_Charge] FOREIGN KEY([CHARGE_ID])
REFERENCES [dbo].[tbl_VAS_Charge] ([CHARGE_ID])
ON UPDATE CASCADE
GO
ALTER TABLE [dbo].[tbl_Key_Map_Master] CHECK CONSTRAINT [FK_tbl_Key_Map_Master_tbl_VAS_Charge]
GO
ALTER TABLE [dbo].[tbl_Key_Map_Master] ADD CONSTRAINT [DF_tbl_Key_Map_Master_SERVICE_CODE] DEFAULT (newid()) FOR [SERVICE_CODE]
GO
ALTER TABLE [dbo].[tbl_Key_Map_Master] ADD CONSTRAINT [DF_tbl_Key_Map_Master_WORD_ASSIGN] DEFAULT ('N') FOR [KEYWORD_ASSIGN]
GO
ALTER TABLE [dbo].[tbl_Key_Map_Master] ADD CONSTRAINT [DF_tbl_Key_Map_Master_EXPIRED] DEFAULT ('N') FOR [EXPIRED]
GO
ALTER TABLE [dbo].[tbl_Key_Map_Master] ADD CONSTRAINT [DF_tbl_Key_Map_Master_TIME_STAMP] DEFAULT (getdate()) FOR [TIME_STAMP]
GO
ALTER TABLE [dbo].[tbl_VAS_Charge] ADD CONSTRAINT [DF_tbl_VAS_Charge_RECORD_NO] DEFAULT (newid()) FOR [RECORD_NO]
GO
ALTER TABLE [dbo].[tbl_VAS_Charge] ADD CONSTRAINT [DF_tbl_VAS_Charge_EXPIRED] DEFAULT ('N') FOR [EXPIRED]
GO
ALTER TABLE [dbo].[tbl_VAS_Charge] ADD CONSTRAINT [DF_tbl_VAS_Charge_TIME_STAMP] DEFAULT (getdate()) FOR [TIME_STAMP]
GO
USE [Banglalink_Charge_N_Archive]
GO
/****** Object: Table [dbo].[tbl_Outbox_Charging_07] Script Date: 07/19/2016 10:49:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Outbox_Charging_07](
[TranId] [uniqueidentifier] NULL,
[ServiceId] [int] NULL,
[TID] [bigint] NULL,
[MSISDN] [varchar](13) NULL,
[Code] [varchar](20) NULL,
[Processtime] [datetime] NULL,
[Lastupdate] [datetime] NULL,
[Status] [varchar](5) NULL,
[Retry] [int] NULL,
[Amount] [money] NULL,
[Expire] [varchar](1) NULL,
[type] [varchar](2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [Banglalink_Charge_N_Archive]
GO
/****** Object: Table [dbo].[tbl_Outbox_Response_07] Script Date: 07/19/2016 10:49:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Outbox_Response_07](
[Tranid] [uniqueidentifier] NULL,
[Out_Tranid] [varchar](100) NULL,
[Request_Time] [varchar](50) NULL,
[Resonse_Time] [varchar](50) NULL,
[Status] [varchar](50) NULL,
[TID] [varchar](50) NULL,
[Response] [varchar](500) NULL,
[Process_Time] [datetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

imneaz (7/18/2016)
Dear Expert ,
I have a problem ...please help me out..
Conversion failed when converting the varchar value '201ex' to data type int.

Quick question, do you have any sample data to go with the DDL? Obviously the '201ex' value will not convert to an integer, now the question is whether it is an exponential value or not?

😎

BTW, from the looks of the query, it seems rather complex for the relatively simple operations.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

They only Implicit type casting to INT in your complete query it the following line:

WHERE Req.Status <> 100 AND CAST(Req.Processtime as DATE)= '2016-07-18'

As per your Table Definition Field Req.Status is Varchar

/****** Object: Table [dbo].[tbl_Outbox_Charging_07] Script Date: 07/19/2016 10:49:06 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[tbl_Outbox_Charging_07](

[TranId] [uniqueidentifier] NULL,

[ServiceId] [int] NULL,

[TID] [bigint] NULL,

[MSISDN] [varchar](13) NULL,

[Code] [varchar](20) NULL,

[Processtime] [datetime] NULL,

[Lastupdate] [datetime] NULL,

[Status] [varchar](5) NULL,

[Retry] [int] NULL,

[Amount] [money] NULL,

[Expire] [varchar](1) NULL,

[type] [varchar](2) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

So you need to Adjust your query like this:

WHERE Req.Status <> '100' AND CAST(Req.Processtime as DATE)= '2016-07-18'

Your complete query will look like this:

SELECT

LTRIM(RTRIM(CONVERT(varchar(100),Req.TID)))AS TransactionID,'GakkMedia' AS PartnerName,LTRIM(RTRIM(Req.MSISDN)) AS MSISDN ,

LTRIM(RTRIM(VC.ORIGIN_PORT)) AS [ShortCode], 'PushpullContent' AS ContentName, LTRIM(RTRIM(Req.TranId)) AS ContentID, '' AS ServiceID,

LTRIM(RTRIM(Req.Code)) AS [Subscription group id], LTRIM(RTRIM(Req.Code)) AS [Charging code], '' AS ContentType, LTRIM(RTRIM(Req.Amount)) AS ContentPrice, LTRIM(RTRIM(KMM.CHANNEL_TYPE)) AS [Channel Type],

'Subscription' AS ChargeType, LTRIM(RTRIM(Req.Code)) AS ChargingReference, LTRIM(RTRIM(Req.Amount)) AS RequestedAmount,

'debit' AS TransactionType, CASE Req.Status WHEN '100' THEN 'Success' ELSE 'FAILED' END AS TransactionStatus,

Req.Status AS ResultCode ,

RIGHT('00' + CONVERT(varchar(2), DATEPART(month, Res.Request_Time)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(day, Res.Request_Time)), 2) +

RIGHT('0000' + CONVERT(varchar(4), DATEPART(year, Res.Request_Time)), 4) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(HOUR, Res.Request_Time)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(MINUTE, Res.Request_Time)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(SECOND, Res.Request_Time)), 2) AS RequestTimestamp,

RIGHT('00' + CONVERT(varchar(2), DATEPART(month, Res.Resonse_Time)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(day, Res.Resonse_Time)), 2) +

RIGHT('0000' + CONVERT(varchar(4), DATEPART(year, Res.Resonse_Time)), 4) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(HOUR, Res.Resonse_Time)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(MINUTE, Res.Resonse_Time)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(SECOND, Res.Resonse_Time)), 2) AS ResponseTimestamp,

RIGHT('00' + CONVERT(varchar(2), DATEPART(month, GETDATE() + 1)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(day, GETDATE() + 1)), 2) +

RIGHT('0000' + CONVERT(varchar(4), DATEPART(year, GETDATE() + 1)), 4) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(HOUR, GETDATE() + 1)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(MINUTE, GETDATE() + 1)), 2) +

RIGHT('00' + CONVERT(varchar(2), DATEPART(SECOND, GETDATE() + 1)), 2) AS NextChargingDate,

CONVERT(VARCHAR(10), 'Subscribe') AS SubscriptionStatus,

'' AS TotalConsumedUnit, LTRIM(RTRIM(Res.TID)) AS [Transaction ID of SDP]

INTO #tmp2

FROM Banglalink.dbo.tbl_Key_Map_Master AS KMM JOIN Banglalink.dbo.tbl_VAS_Charge AS VC ON KMM.CHARGE_ID = VC.CHARGE_ID

JOIN dbo.tbl_Outbox_Charging_07 AS Req ON Req.ServiceId = KMM.SERVICE_ID

LEFT JOIN dbo.tbl_Outbox_Response_07 AS Res ON Res.Out_Tranid = Req.TranId AND Req.ServiceId =KMM.SERVICE_ID

WHERE Req.Status <> '100' AND CAST(Req.Processtime as DATE)= '2016-07-18'

hope it helps.

imneaz Say Hey Kid Points: 711 More actions · Answer 3

Dear twin.devil

I already changed. and yes it works.

Thanks a lot.

Umer Akram SSC-Insane Points: 22209 More actions · Answer 4

Thanks for your feedback. you can also change your following query:

SELECT RIGHT('00' + CONVERT(VARCHAR(2), Datepart(month, Getdate())), 2)

+ RIGHT('00' + CONVERT(VARCHAR(2), Datepart(day, Getdate())), 2)

+ RIGHT('0000' + CONVERT(VARCHAR(4), Datepart(year, Getdate())), 4)

+ RIGHT('00' + CONVERT(VARCHAR(2), Datepart(hour, Getdate())), 2)

+ RIGHT('00' + CONVERT(VARCHAR(2), Datepart(minute, Getdate())), 2)

+ RIGHT('00' + CONVERT(VARCHAR(2), Datepart(second, Getdate())), 2) AS RequestTimestamp

to something smaller like this:

SELECT Replace(CONVERT(VARCHAR(20), Getdate(), 101), '/', '')

+ Replace(CONVERT(VARCHAR(20), Getdate(), 108), ':', '') AS RequestTimestamp