You must Sign In to post a response.
  • Category: SQL Server

    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

    Kindly help what mistake I am doing.
  • #769334
    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.


    Anjali Bansal

    ~Give your best and lead the world

  • #769335
    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
    SELECT * FROM Master WHERE Dt_Birth<@Dt_Birth

    Sridhar Thota.
    Editor: DNS Forum.

  • Sign In to post your comments