How to pass a date function in a dynamic sql query in sql server

Question

SELECT @sql='

select 
    *
from (
    select ''Ongoing'' AS Ongoing,
    Coalesce(COUNT(project),0) AS project, Coalesce(COUNT(year(u.PlannedStartDate)),0) as [y]
    from Projects u  WHERE 
    u.actualstartdate IS  NULL 
    AND u.Startdate < ''+GETDATE()+''
    AND ID ='''+@ID+'''  

  ) Data
PIVOT (
  COUNT(project)
  FOR [y]
  IN (
    ' + @Years + '
  )
) PivotTable
'

Here I want to pass the cur date but it's not working.. this is because the value of GETDATE() is not coming in the string

Welcome to StackOverflow: if you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! — marc_s
– marc_s, Commented Jul 11, 2012 at 14:36

Chandu · Accepted Answer · 2012-07-11 14:37:54Z

2

change AND u.Startdate < ''+GETDATE()+'' to AND u.Startdate < GETDATE()

answered Jul 11, 2012 at 14:37

Chandu

83.2k19 gold badges137 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

amsprich · Accepted Answer · 2012-07-11 14:39:28Z

1

Just remove the quotes around it I would think

SELECT @sql='

select * from ( select ''Ongoing'' AS Ongoing, Coalesce(COUNT(project),0) AS project,
Coalesce(COUNT(year(u.PlannedStartDate)),0) as [y] from Projects u 
WHERE u.actualstartdate IS   NULL AND u.Startdate < GETDATE() AND ID ='''+@ID+'''

) Data PIVOT ( COUNT(project) FOR [y] IN ( ' + @Years + ' ) ) PivotTable '

answered Jul 11, 2012 at 14:39

amsprich

2011 silver badge8 bronze badges

1 Comment

synccm2012 Over a year ago

This will not work in a string function. It takes as GETDATE() only not the date value.

Rick Gittins · Accepted Answer · 2012-07-11 14:37:50Z

0

Try casting GETDATE() to a varchar like this: CAST(GETDATE() AS varchar)

answered Jul 11, 2012 at 14:37

Rick Gittins

1,1381 gold badge9 silver badges24 bronze badges

Collectives™ on Stack Overflow

How to pass a date function in a dynamic sql query in sql server

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related