Case When on @prompt

[former_member419024]

I get SQL query error if I get Year(Getdate()) inside the Case statement, but it ok when Getdate() in derived table.

(CASE WHEN @prompt('Enter This Year','N','Year Others',mono,constrained,persistent,{'9999'}) = '9999' then Getdate() else @prompt('Enter This Year','N','Year Others',mono,constrained,persistent,{'9999'}) END) ---- I receive no error.

(CASE WHEN @prompt('Enter This Year','N','Year Others',mono,constrained,persistent,{'9999'}) = '9999' then Year(Getdate()) else @prompt('Enter This Year','N','Year Others',mono,constrained,persistent,{'9999'}) END) ---- I received SQL Query error.

Trying to convert Year(Getdate()) to numeric because data type of @prompt is in 'N' but still I get SQL Query error.

[nscheaffer · Accepted solution]

Is the database SQL Server?  If so, try this...

DATEPART(YEAR, GETDATE())

Noel