Category: SQL Server

21 Aug 2017 11:41

Joined:	15/03/2007
Level:	Silver
Points:	101

Points:	5

How to pass date time in stored procedure through variable.

Dear members
Greetings for the day.

I have written a stored procedure which required Input Parameter Date and a query which fetches the data as per that date.

Query is as follows:

select * from Master where Dt_Birth<@Dt_Birth (not working)
select * from Master where Dt_Birth<'1964-10-01 00:00:00' (working)
column Dt_Birth is smalldatetime, not null
In stored procedure I have declared @Dt_Birth as datetime

ALTER Proc [sp_Offer_dynamic]
(
@Program varchar(500),
@Dt_Birth datetime
)
as
Begin

Kindly help what mistake I am doing.

#769334

21 Aug 2017 15:39

Joined:	13/01/2015
Level:	Silver
Points:	230

Points:	2

Hi Abhishek,

Instead of datetime parameter , please declare it as Date only.Then u can call your SP as :

exec [sp_Offer_dynamic] @Dt_Birth='08/21/2017'

It will work.

~cheers!!!!!

Thanks!
Anjali Bansal

~Give your best and lead the world

#769335

22 Aug 2017 11:14

Joined:	12/02/2013
Level:	Gold
Points:	3098

Points:	3

Hi Abhishek.

Try below sql script it is working.
After modifying the procedure execute the below line in your query window
EXEC sp_Offer_dynamic '02/03/2017'


-- EXEC sp_Offer_dynamic '02/03/2017'
CREATE PROCEDURE sp_Offer_dynamic
(
@Dt_Birth datetime
)
AS
BEGIN
SELECT * FROM Master WHERE Dt_Birth<@Dt_Birth 
END

Sridhar Thota.
Editor: DNS Forum.

How to pass date time in stored procedure through variable.

About dotnetspider.com

Quick Links

General

Services