I have several stored procedures that take a date/time input, one of which takes a date only in the form of a nvarchar because it utilizes dynamic SQL. When I try to cast the variable going into the stored procedure, I get an error. This doesn't make much sense to me.

I know I can work around it by creating a second set of nvarchar variables or casting within the stored procedure, but I'd like some insight on why I can't just cast the variable in-line.

Example. *TOP is a Select statement telling me my NVARCHAR DATE kicks out just fine.

MID is where SSMS is throwing the error, BOTTOM is where SSMS thinks it's just fine.*

stored procedure wtfman

  • Why are you casting a date as nvarchar then casting back to date? Also stored procedure parameters can't take expressions - only constants and variables. Finally, don't use nvarchar without length, and never mind why is this a string, why is it nvarchar instead of varchar? Jan 29, 2015 at 19:05
  • 1. In sprocs called before this one I am using datetime, in this one I only want the date. 2. I'm not, the first is a select, not a set 3. OK, that explains it. 4. Will do. 5. Because the sproc uses dynamic SQL, as stated in the question.
    – n8.
    Jan 29, 2015 at 19:08
  • Dynamic SQL (or date only) are terrible excuses for converting a date to a string. Your dynamic SQL should still be safely parameterizing a date variable through sp_executesql, not concatenating and double-single-quoting some date that has been changed to a string. Jan 29, 2015 at 19:10
  • I didn't know that, thanks for your answer.
    – n8.
    Jan 29, 2015 at 19:11

1 Answer 1


Stored procedure parameters can only accept literals, constants or variables as parameters. It can't take expressions of any kind.

You can say:

EXEC dbo.myprocedure @foo = 'bar';

But you can't say:

EXEC dbo.myprocedure @foo = 'b' + 'ar';

You should not be converting a date to a string and then back to a date again. Your dynamic SQL can easily take a date parameter, parameterized safely (you may want to read up on "SQL injection"), and not have to worry about ugly string concatenation. Here is an example:

  @dt DATE
  SET @sql = N'SELECT DATEPART(DAY, @dt);';
  EXEC sys.sp_executesql @sql, N'@dt DATE', @dt;

DECLARE @fromDay DATETIME = '20010914 03:45';
DECLARE @dt DATE = @fromDay; -- or just declare @fromDay as DATE in the first place;
EXEC dbo.foo;



Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.