CODEWITHSUNDEEP
sql-server
synccm2012
synccm2012

Reputation: 497

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

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

Upvotes: 0

Views: 1708

Answers (3)

amsprich
amsprich

Reputation: 201

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 '

Upvotes: 1

Chandu
Chandu

Reputation: 82903

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

Upvotes: 2

Rick Gittins
Rick Gittins

Reputation: 1138

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

Upvotes: 0

Related Questions