date difference - 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

date difference

Author

Topic

BCJ
Starting Member

42 Posts

Posted - 2008-07-15 : 14:25:03

Hi,
I have a query like below in a procedure and
the datatype of P1S and P1E are datetime.

when i'm executing the procedure getting an error
"Operand data type varchar is invalid for subtract operator"

select convert(nvarchar,cast(@P1S as datetime),1)+'-'+convert(nvarchar,cast(@P1E as datetime),1) as groupdate from table_1...

please help. thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 14:27:30

[code]SELECT CONVERT(NVARCHAR(10), @P1S, 1) + '-' + CONVERT(NVARCHAR(10), @P1E, 1) AS GroupDate
FROM table_1[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 14:27:45

specify a length for nvarchar while casting. Also where are you using the select?

BCJ
Starting Member

42 Posts

Posted - 2008-07-15 : 14:58:53

here is the procedure where i'm
doing the conversion, still getting the same error........thanks.

ALTER PROCEDURE rptdataset1

@P1S datetime,
@P1E datetime,
@sid varchar(1000),
@cbT int

as

DECLARE @string1 VARCHAR(5000)

create table #temp(f1 nvarchar(50), f2 nvarchar(50), f3 nvarchar(30))
set @string1 ='
insert into #temp(f1,f2,f3)
SELECT t1.f1, t2.f2 , CONVERT(NVARCHAR(10), @P1S, 1) + '-' + CONVERT(NVARCHAR(10), @P1E, 1) as f3
FROM t1 ,t2,t3
WHERE (t1.f1 in (' + @Sid + ')) AND (t2.f2 = ' + cast(@cbT as varchar(30)) + ' )
AND (cast(t3.f1 as datetime) BETWEEN ''' + CAST(@P1S AS varchar(30)) + ''' AND ''' + CAST(@P1E AS varchar(30)) + ''' ) '

exec(@string1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 15:03:06

+ '-' +

should be

+ ''-'' +

if you use dynamic sql.

instead of just EXEC, next time do a print @string1 instead to spot where the error is!

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 15:03:13

modify like this

set @string1 ='
insert into #temp(f1,f2,f3)
SELECT t1.f1, t2.f2 , CONVERT(NVARCHAR(10), @P1S, 1) + ''-'' + CONVERT(NVARCHAR(10), @P1E, 1) as f3
FROM t1 ,t2,t3 
WHERE (t1.f1 in (' + @Sid + ')) AND (t2.f2 = ' + cast(@cbT as varchar(30)) + ' ) 
AND (cast(t3.f1 as datetime) BETWEEN ''' + CAST(@P1S AS varchar(30)) + ''' AND ''' + CAST(@P1E AS varchar(30)) + ''' ) '

BCJ
Starting Member

42 Posts

Posted - 2008-07-15 : 15:19:24

tried as suggested and getting an error "Must declare the scalar variable "@P1S"."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 15:28:29

[code]set @string1 ='
insert into #temp(f1,f2,f3)
SELECT t1.f1, t2.f2 , ''' + CONVERT(NVARCHAR(10), @P1S, 1) + '-' + CONVERT(NVARCHAR(10), @P1E, 1) + ''' as f3
FROM t1 ,t2,t3
WHERE t1.f1 in (' + @Sid + ') AND t2.f2 = ' + cast(@cbT as varchar(30)) + '
AND cast(t3.f1 as datetime) BETWEEN ''' + CONVERT(NVARCHAR(10), @P1S, 112) + ''' AND ''' + CONVERT(NVARCHAR(10), @P1E, 112) + ''''

exec( @string1)[/code]

E 12°55'05.25"
N 56°04'39.16"

BCJ
Starting Member

42 Posts

Posted - 2008-07-15 : 15:33:51

Yes that worked fine. thanks a lot...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 15:36:35

All I did was a PRINT of the @String1 content and then I spotted the errors very rapidly.
I suggest you do the same next time you are over your head with dynamic sql.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources