need help on dynamic query with date

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)

need help on dynamic query with date

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2012-11-25 : 00:57:49

My scripts as following,


declare @collectDte_From date
declare @collectDte_To date
declare @chequeNo varchar(20)

Declare @SQL VarChar(max)    

SELECT @SQL = 'select crtDte as tarikhKutipan, upper(chequeNo) as chequeNo , chequeDte,
    zakatAmount, upper(t2.descrp) as chequeStat
    from dbo.paymentCheque_SPZB t1
    inner join dbo.chequeStat t2
    on t1.chequeStat=t2.idx' 

if @collectDte_From is null and @collectDte_To is null and @chequeNo is null
Begin
	SELECT @SQL = @SQL
End
else
Begin
	SELECT @SQL = @SQL + ' where 1=1 '
End

if @collectDte_From is not null and @collectDte_To is not null
Begin
	SELECT @SQL = @SQL + 'and CONVERT(date,crtDte)>=' + @collectDte_From + ' '
End

Exec ( @SQL)

I got an error as following,
Msg 402, Level 16, State 1, Line 24
The data types varchar(max) and date are incompatible in the add operator.

Please help me

Luuk123
Yak Posting Veteran

52 Posts

Posted - 2012-11-25 : 07:25:30

In this select statement:
SELECT @SQL = @SQL + 'and CONVERT(date,crtDte)>=' + @collectDte_From + ' '

You are adding @collectDte_From, which is a date, to @SQL, which is a varchar.

+ put a space between ' and and : "@SQL + ' and CON"

Elizabeth B. Darcy
Starting Member

39 Posts

Posted - 2012-11-25 : 08:24:37

Change that last "SELECT @SQL =" statement to this:


SELECT @SQL = @SQL + 'and CONVERT(date,crtDte)>=''' + CONVERT(varchar(32), @collectDte_From, 112) + ''' '

Having said that, may I suggest that you consider doing this without using dynamic SQL? Based on what you posted, I see no reason to use dynamic SQL. If you must use dynamic SQL, another option is to use sp_executesql,which offers some advantages.

________________________________________
-- Yes, I am indeed a fictional character.

wkm1925
Posting Yak Master

207 Posts

Posted - 2012-11-25 : 10:10:16

tq to Luuk123, and Elizabeth B. Darcy

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-11-29 : 06:18:43

You don't need a dynamic sql at all. Just use this

declare @collectDte_From date
declare @collectDte_To date
declare @chequeNo varchar(20)

select crtDte as tarikhKutipan, upper(chequeNo) as chequeNo , chequeDte,
zakatAmount, upper(t2.descrp) as chequeStat
from dbo.paymentCheque_SPZB t1
inner join dbo.chequeStat t2
on t1.chequeStat=t2.idx
where
(@collectDte_From is null and @collectDte_To is null and @chequeNo is null)
or
(@collectDte_From is not null and @collectDte_To is not null and crtDte>=@collectDte_From)

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources