When I have to debug Stored Procedures I often trace the exact RPC call that is made with the SQL Profiler. I then look at the variable declarations that were used when to call the Stored Procedure and use these declarations to debug the procedure.
If one parameter is of datatype datetime I declare the variable as follows:
DECLARE @MyDate datetime = '2017-05-12 00:00:00'
Note that this is also the format the SQL Profiler uses to show the call.
When I then print @MyDate, it is not the 12th of May which I would expect. Instead, SQL Server interprets it as the 5th of December.
SET DATEFORMAT DMY
PRINT @MyDate
yields
Dec 5 2017 12:00AM
How can I achieve the expected result, that is, that PRINT @MyDate yields May 12 2017 12:00AM.
UPDATE:
As requested in the comments, I show which part I am pulling from the SQL Profiler. The profiler shows this call:
exec NAME_OF_STORED_PROCEDURE @MyDate'2017-05-12 00:00:00'
I then use the @MyDate'2017-05-12 00:00:00' and change it to DECLARE @MyDate = '2017-05-12 00:00:00'
Parameters passed via an RPC call are sent to SQL Server in native (binary) format. The text you see in a Profiler (or Extended Events visualization) is just a reverse-engineered rendering of the native values passed via the TDS protocol. Unfortunately, the string format displayed is not a neutral ISO date so you need to tweak the format manually if you copy/paste the text.
I filed this feedback suggestion on connect 10 years ago to use a neutral date format. Please upvote.