Passing Date parameter to Stored Procedure

Question

Post reply

Passing Date parameter to Stored Procedure

Rick Harper

SSC Veteran

Points: 275
More actions
October 9, 2014 at 5:14 am

#397484

I'm hoping someone can help. I have a fair bit of SQL experience but struggle with TSQL
I am trying to pass a date parameter to a stored procedure.
I pass the actual date as a string but keep getting errors that the string variable cannot be converted to a date whatever I do.
Basically, this works:
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, '2004-01-01',101 )
But this returns an error:
DECLARE @strdate VARCHAR
SET @strdate = '2004-01-01'
DECLARE @DDate DATETIME
SET @DDate = convert(datetime, @strdate,101 )
What am I doing wrong?

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 1

Shame on you for not specifying the length of the VARCHAR variable

If you do not specify the length of the VARCHAR datatype, it takes 1 as default.

Basically your @strdate variable is getting populated with '2', which obviously is not a date.

ps: welcome to the forum by the way

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 247158 More actions · Answer 2

Try running this and the answer will become clear:

DECLARE @strdate varchar

SET @strdate = '2004-01-01'

select @strdate

By declaring your variable as Varchar but without a length, SQL Server assumes a length of 1 character by default.

Use DECLARE @strdate varchar(10) or similar and your problem goes away.

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 3

Phil Parkin (10/9/2014)
Try running this and the answer will become clear:
DECLARE @strdate varchar
SET @strdate = '2004-01-01'
select @strdate
By declaring your variable as Varchar but without a length, SQL Server assumes a length of 1 character by default.
Use DECLARE @strdate varchar(10) or similar and your problem goes away.

Too slow

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Rick Harper SSC Veteran Points: 275 More actions · Answer 4

An old friend has come up with the answer so the riddle is solved

I just replace:

DECLARE @strdate VARCHAR

with

DECLARE @strdate VARCHAR(15)

and everything works fine.

Rick Harper SSC Veteran Points: 275 More actions · Answer 5

Takes 1 as the default hey?

Well I didn't know that.

Thanks - I am now a happy bunny.

Phil Parkin SSC Guru Points: 247158 More actions · Answer 6

Rick Harper (10/9/2014)
An old friend has come up with the answer so the riddle is solved
I just replace:
DECLARE @strdate VARCHAR
with
DECLARE @strdate VARCHAR(15)
and everything works fine.

Never mind your old friend. Your two new friends, Koen and Phil, beat him to it, and in public

Koen Verbeeck SSC Guru Points: 259165 More actions · Answer 7

Phil Parkin (10/9/2014)
Never mind your old friend. Your two new friends, Koen and Phil, beat him to it, and in public

:w00t:

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Grant Fritchey SSC Guru Points: 399061 More actions · Answer 8

I wouldn't suggest passing the value as a string. You're potentially taking away the ability of the optimizer to use parameter sniffing (a good thing the majority of the time) and you're possibly looking at implicit conversion issues which affect statistics use. If you have a date or datetime value, use a date or datetime parameter or variable.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Passing Date parameter to Stored Procedure

Cookies on SQLServerCentral