[SOLVED] EXEC sp_executesql with a datetime parameter?

EXEC sp_executesql with a datetime parameter?

I'm using EXEC sp_executesql for a dynamic query in SQL Server 2016, and am stumbling on when a user wants to pass in a year. I have a datetime field called tkemdate and it is stored as a datetime field in SQL.

Although SQL stores it as datetime, the user only passes in a year parameter (2020, 2019, 2018, etc). How do I get the query accept just the year?

Here's my stored procedure, with the datetime param.

(
@tkemdate datetime
)

AS
BEGIN

Declare  @SQL NVARCHAR(MAX)

Set @SQL = 'SELECT        timekeep.tkinit, timekeep.tkfirst, timekeep.tklast, 
                          year(timekeep.tkemdate) as tkemdate

FROM   abc123.timekeep'        
                     
WHERE  1 = 1

IF @tkemdate IS NOT NULL
Select @SQL = @SQL + 'AND ([tkemdate] = @tkemdate)'
EXEC sp_executesql @SQL, N'@tkemdate datetime',  @tkemdate

END

Solution

You can use something like this:

IF @year IS NOT NULL
    Select @SQL = @SQL + ' AND (YEAR([tkemdate]) = @year)'

EXEC sp_executesql @SQL, N'@year int',  @year=@year;

It is not clear where @year comes from, but you say that the user is passing in the year.

If you only want to use the year from @tkemdate then:

IF @tkemdate IS NOT NULL
    Select @SQL = @SQL + ' AND (YEAR([tkemdate]) = YEAR(@tkemdate))';

EXEC sp_executesql @SQL, N'@@tkemdate datetime',  @@tkemdate=@@tkemdate;