EXEC sp_executesql with a datetime parameter?

Question

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

Why the input partameter (`@tkemdate`) is declared as `datetime`? — Zhorov, Oct 26 '20 at 12:54
That SQL isn't complete, but it appears it's the definition Stored Procedure/Function. The call to `sp_executesql` therefore isn't the "problem" it's the definition of the SP/Function; `@tkemdate` needs to be defined as an `int` if that is what the input parameter is. Otherwise you need to educate the user that `2019` is not a valid `datetime` value, and that they should be using something like `'20190101'`. — Thom A, Oct 26 '20 at 12:55

score 1 · Accepted Answer · answered Oct 26 '20 at 13:06

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;

score 0 · Answer 2 · answered Oct 26 '20 at 14:10

It could be also something like that - where Your procedure has an integer parameter representing year.

We could also add some validation to it, for example if @year is less than 1000 or greater than 2500.

CREATE PROCEDURE abc123.get_timekeep_records (
 @year int
)
AS
BEGIN
 SET NOCOUNT ON; -- I usually add this because of a PHP driver
 DECLARE @SQL NVARCHAR(MAX)

 SET @SQL = 'SELECT timekeep.tkinit, timekeep.tkfirst, timekeep.tklast,
                    YEAR(timekeep.tkemdate) as [tkemdate]
             FROM abc123.timekeep
             WHERE 1 = 1 '

 -- Validation
 IF @year < 1000 OR @year > 2500
   RAISERROR('Invalid year: %i', 16, 1, @year)

 IF @year IS NOT NULL
   SELECT @SQL = @SQL + 'AND (YEAR(timekeep.tkemdate) = @year)'

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

END

EXEC sp_executesql with a datetime parameter?

2 Answers2