How to pass datetime parameter to SP dynamically

Question

Post reply

How to pass datetime parameter to SP dynamically

avdhut.k

SSCrazy

Points: 2834
More actions
October 14, 2013 at 5:16 am

#284500

Hi,In below query I want to pass the parameter whose datatype is Datatime,dynamically,
I am getting following error,
Conversion failed when converting date and/or time from character string.
Declare @P_AsonDate as datetime = Getdate(),@Str_ToDo As varchar(1000)
Set @Str_ToDo = ''
Set @Str_ToDo = 'SpTable_2mail' + ' ' + @P_AsonDate
Print @Str_ToDo
EXEC (@Str_ToDo )
Please help me,
This is urgent to me.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Sean Pearce SSCoach Points: 15999 More actions · Answer 1

DECLARE @P_AsonDate AS DATETIME = GETDATE(),

@Str_ToDo AS NVARCHAR(MAX) = '',

@params AS NVARCHAR(MAX);

SET @Str_ToDo = 'SpTable_2mail @P_AsonDate';

SET @params = '@P_AsonDate AS DATETIME';

EXEC sp_executesql @Str_ToDo, @params, @P_AsonDate = @P_AsonDate;

http://technet.microsoft.com/en-us/library/ms188001.aspx

The SQL Guy @ blogspot[/url]

@SeanPearceSQL

About Me[/url]

PiMané SSChampion Points: 13965 More actions · Answer 2

avdhut.k (10/14/2013)
Hi,In below query I want to pass the parameter whose datatype is Datatime,dynamically,
I am getting following error,
Conversion failed when converting date and/or time from character string.
Declare @P_AsonDate as datetime = Getdate(),@Str_ToDo As varchar(1000)
Set @Str_ToDo = ''
Set @Str_ToDo = 'SpTable_2mail' + ' ' + @P_AsonDate
Print @Str_ToDo
EXEC (@Str_ToDo )
Please help me,
This is urgent to me.

Best way is just like Sean posted...

On your post you need to close the quote marks after date and also need to convert the datetime to a string format with date format..

CREATE PROCEDURE SpTable_2mail @Date DATETIME

AS

SELECT @date

GO

DECLARE @P_AsonDate as datetime = Getdate(),@Str_ToDo As varchar(1000)

Set @Str_ToDo = ''

Set @Str_ToDo = 'SpTable_2mail ''' + CONVERT(VARCHAR(100),@P_AsonDate , 109) + ''''

Print @Str_ToDo

EXEC (@Str_ToDo )

If you need to work better, try working less...

avdhut.k SSCrazy Points: 2834 More actions · Answer 3

avdhut.k

SSCrazy

Points: 2834

October 15, 2013 at 11:35 pm

#1658793

Thanks!!!!!!!!!!!!