Convert help plz.... - 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

General SQL Server Forums

New to SQL Server Programming

Convert help plz....

Author

Topic

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-10 : 09:50:31

Hi, I have this simple query and am not converting correctly. Can anyone help? I keep getting this error: Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.

(SELECT SUM(SALESLINE.LINEAMOUNT) AS REMAIN, SALESLINE.CUSTACCOUNT,
CAST(SALESLINE.CREATEDDATE AS DATETIME) AS CREATEDDATE
FROM SALESLINE
WHERE (SALESLINE.DATAAREAID = @Company) AND (CREATEDDATE > DATEADD(YEAR,-1,GETDATE()))
AND (SALESLINE.CREATEDDATE < getDate()))

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-10 : 09:55:02

Using convert and still same error! What I am trying to do is look for all saleslines from todays date minus one year.

(SELECT SUM(SALESLINE.LINEAMOUNT) AS REMAIN, SALESLINE.CUSTACCOUNT,
CONVERT(DATETIME, SALESLINE.CREATEDDATE) AS CREATEDDATE
FROM SALESLINE
WHERE (SALESLINE.DATAAREAID = @Company) AND (CREATEDDATE > DATEADD(YEAR,-1,GETDATE()))
AND (SALESLINE.CREATEDDATE < getDate())) AS T7 ON T1.ACCOUNTNUM = T7.CUSTACCOUNT

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-10 : 10:02:04

Hello again,

The field SALESLINE.CREATEDDATE is already of type DateTime. Why do I need to convert???

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-10 : 10:11:10

Whats the error you get whe you dont convert or cast and just use..

(SELECT SUM(LINEAMOUNT) AS REMAIN, CUSTACCOUNT,
CREATEDDATE
FROM SALESLINE
WHERE (DATAAREAID = @Company) AND (CREATEDDATE > DATEADD(YEAR,-1,GETDATE()))
AND (CREATEDDATE < getDate()))

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-10 : 10:16:02

[code]SELECT SUM(LINEAMOUNT) AS REMAIN,
CUSTACCOUNT,
CREATEDDATE
FROM SALESLINE
WHERE CAST(DATAAREAID AS INT) = @Company
AND CREATEDDATE > DATEADD(YEAR,-1,GETDATE())
AND CREATEDDATE < GETDATE()
GROUP BY CUSTACCOUNT,
CREATEDDATE[/code]

E 12°55'05.63"
N 56°04'39.26"

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-10 : 10:18:25

quote:
Originally posted by vijayisonly

Whats the error you get whe you dont convert or cast and just use..

(SELECT SUM(LINEAMOUNT) AS REMAIN, CUSTACCOUNT,
CREATEDDATE
FROM SALESLINE
WHERE (DATAAREAID = @Company) AND (CREATEDDATE > DATEADD(YEAR,-1,GETDATE()))
AND (CREATEDDATE < getDate()))

The same error msg:

Implicit conversion from data type sql_variant to int is not allowed. Use the CONVERT function to run this query.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-10 : 10:19:25

quote:

Originally posted by Peso

SELECT		SUM(LINEAMOUNT) AS REMAIN,
		CUSTACCOUNT, 
		CREATEDDATE
FROM		SALESLINE
WHERE		CAST(DATAAREAID AS INT) = @Company
		AND CREATEDDATE > DATEADD(YEAR,-1,GETDATE())
		AND CREATEDDATE < GETDATE()
GROUP BY	CUSTACCOUNT, 
		CREATEDDATE

E 12°55'05.63"
N 56°04'39.26"

Cast dataareaid??? No need to cast this? Can I ask why you suggest this???

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 10:20:08

quote:
Originally posted by SQLSoaker

Using convert and still same error! What I am trying to do is look for all saleslines from todays date minus one year.

(SELECT SUM(SALESLINE.LINEAMOUNT) AS REMAIN, SALESLINE.CUSTACCOUNT,
CONVERT(DATETIME, SALESLINE.CREATEDDATE) AS CREATEDDATE
FROM SALESLINE
WHERE (SALESLINE.DATAAREAID = @Company) AND (CREATEDDATE > DATEADD(YEAR,-1,GETDATE()))
AND (SALESLINE.CREATEDDATE < getDate())) AS T7 ON T1.ACCOUNTNUM = T7.CUSTACCOUNT

Look like you haven't shown us full query. Why column has type Sql_variant? Post full query.

SQLSoaker
Posting Yak Master

169 Posts

Posted - 2009-03-10 : 10:24:07

Got it everyone thanks for help. I am using Visual Studio to write these reports. This one was taken from a SP and I had commented out my variable declarations at the top of my query:

DECLARE @EnteredDate AS datetime DECLARE @Company AS char(10)
SET @EnteredDate = GetDate()
SET @Company = 'xxx'

I uncommented this (hence the sql_variant) and it worked. Thanks again for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources