conversion failed error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

conversion failed error

Author

Topic

accessdbguru
Starting Member

26 Posts

Posted - 2009-08-31 : 11:36:04

I get this error:

Conversion failed when converting the varchar value "/" to data type smallint.

My fields are:

WS_job_number = char(17)
periodid = smallint
year1 = smallint
contract_earned_curr_mo = numeric(19,5)

Here is my sql:

SELECT dbo.[vw_union].WS_Job_Number,
dbo.[vw_union].PERIODID,
dbo.[vw_union].YEAR1,
dbo.[vw_union].Contract_Earned_Curr_Mo,
isnull([contract_earned_curr_mo]-(SELECT contract_earned_curr_mo FROM dbo.[vw_union] T3 where
T3.ws_job_number = dbo.[vw_union].ws_job_number and Convert(DateTime, Year1 + '/ ' + PeriodId + '/ ' + 1 ,103) IN
(
SELECT MAX
(Convert(DateTime, Year1 + '/ ' + PeriodId + '/ ' + 1 ,103)) FROM dbo.[vw_union] T1 WHERE Convert(DateTime, Year1 + '/ ' + PeriodId + '/ ' + 1 ,103) <
Convert(DateTime, dbo.[vw_union].Year1 + '/ ' + dbo.[vw_union].PeriodId + '/ ' + 1 ,103)
AND T1.ws_Job_Number = dbo.[vw_union].ws_job_Number)),contract_earned_curr_mo) AS Result FROM dbo.[vw_union]
ORDER BY dbo.[vw_union].WS_Job_Number, dbo.[vw_union].PERIODID, Convert(DateTime, dbo.[vw_union].Year1 + '/ ' + dbo.[vw_union].PeriodId + '/ ' + 1 ,103) DESC;

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-08-31 : 15:29:38

Your query is failing on an implicit conversion. So, understanding the difference between EXPLICIT and IMPLICIT conversions would be a good place to do a little research.

But the gist is that you are concatenating a STRING and an INT. Sql defaults to trying to trying to CAST the string as an int and "/" obviously cannot be cast as an INT, thus the error.

There are several ways to solve the issue. Here are a couple of alternative/samples:

Convert(DateTime, CAST(Year1 AS VARCHAR(4)) + '/ ' + CAST(PeriodId AS VARCHAR(2)) + '/ 1' ,103)
Convert(DateTime, (Year1 * 10000) + (PeriodId * 100) + 1 ,103)

EDIT: Also, I don't see any need to format that date (Using the format 103).

accessdbguru
Starting Member

26 Posts

Posted - 2009-09-01 : 11:45:01

I see the following errors.

1)Error in Select clause
2)Missing FROM clause
3)FROM not recognized
4) ',' not recognized

My code is:

SELECT dbo.[vw_union].WS_Job_Number,
dbo.[vw_union].PERIODID,
dbo.[vw_union].YEAR1,
dbo.[vw_union].Contract_Earned_Curr_Mo,
isnull([contract_earned_curr_mo]-(SELECT contract_earned_curr_mo FROM dbo.[vw_union] T3 where
T3.ws_job_number = dbo.[vw_union].ws_job_number and Convert(DateTime, CAST(Year1 AS VARCHAR(4)) + '/ ' + CAST(PeriodId AS VARCHAR(2)) + '/ 1' ,103)
IN
(SELECT MAX Convert(DateTime, CAST(Year1 AS VARCHAR(4)) + '/ ' + CAST(PeriodId AS VARCHAR(2)) + '/ 1' ,103)
FROM dbo.[vw_union] T1 WHERE Convert(DateTime, CAST(Year1 AS VARCHAR(4)) + '/ ' + CAST(PeriodId AS VARCHAR(2)) + '/ 1' ,103) < Convert(DateTime, CAST(dbo.[vw_union].Year1 AS VARCHAR(4)) + '/ ' + CAST(dbo.[vw_union].PeriodId AS VARCHAR(2)) + '/ 1' ,103) AND
T1.ws_Job_Number = dbo.[vw_union].ws_job_Number)),contract_earned_curr_mo) AS Result
FROM dbo.[vw_union] ORDER BY dbo.[vw_union].WS_Job_Number, dbo.[vw_union].PERIODID, Convert(DateTime, CAST(dbo.[vw_union].Year1 AS VARCHAR(4)) + '/ ' + CAST(dbo.[vw_union].PeriodId AS VARCHAR(2)) + '/ 1' ,103) DESC

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-01 : 13:49:18

ISNULL has a check expression but no replacement value.

accessdbguru
Starting Member

26 Posts

Posted - 2009-09-01 : 14:03:53

I have modified the end of sql statement with by adding a 0 for IsNull:

FROM dbo.[vw_union] ORDER BY dbo.[vw_union].WS_Job_Number, dbo.[vw_union].PERIODID, Convert(DateTime, (Year1 * 10000) + (PeriodId * 100) + 1 ,103,0) DESC

But I still get the same error.
Please advise.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-01 : 15:06:44

Sorry, I missed that the ISNULL does have a replacement value of "contract_earned_curr_mo."

Any chance you can supply some sample data and expected output (with DDL and all that)? I bet we can rewrite this.
[url]http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx[/url]

accessdbguru
Starting Member

26 Posts

Posted - 2009-09-01 : 16:03:04

Lamprey. First of all I thank you for offering me your help.

CREATE TABLE [dbo].[vw_union]
(WS_JOB_NUMBER][char](17) ,[PERIODID] [smallint] NULL, [YEAR1] [smallint] NULL,
[contract_earned_CURR_MO] [numeric](19, 5) NULL

GO

INSERT RECORDS VALUES:

004534 12 2008 5000.00000
004534 11 2008 3030.00000
004534 10 2008 1030.00000
002111 9 2008 1201.00000
002111 8 2008 1099.00000

OUTPUT SHOULD BE:
4534 12 2008 1970
4534 11 2008 2000
4534 10 2008 1030
2111 9 2008 102
2111 8 2008 1099

Waiting for your reply.
SMS

accessdbguru
Starting Member

26 Posts

Posted - 2009-09-01 : 16:32:56

Sorry! I missed this...

INSERT INTO [dbo].[vw_union] values (ws_job_number,periodid,year1,contract_earned_curr_mo)
VALUES ('004534',12,2008,5000)
VALUES ('004534',11,2008,3030)
VALUES ('004534',10,2008,1030)
VALUES ('002111',9,2008,1201)
VALUES ('002111',8,2008,1099)

Note: There is a gap between month/year periods therefore we need the CONVERT datetime function.

Thank you and waiting for your reply Lamprey!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-01 : 17:17:50

No problem, glad to help..

One question can you have mutiple PeriodIDs for the same year? If not, then this should work:

SELECT
	A.ws_job_number,
	A.PeriodID,
	A.Year1,
	COALESCE(A.contract_earned_CURR_MO - T.contract_earned_CURR_MO, A.contract_earned_CURR_MO)
FROM
	dbo.vw_union AS A
OUTER APPLY
	(
		SELECT MAX(B.contract_earned_CURR_MO) AS contract_earned_CURR_MO
		FROM dbo.vw_union AS B
		WHERE A.ws_job_number = B.ws_job_number
		AND A.PeriodID > B.PeriodID
		AND A.Year1 >= B.Year1
	) AS T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources