SQL Server Performance

Passing the current date as a parameter

Discussion in 'General Developer Questions' started by MooreCock, Dec 20, 2005.

  1. MooreCock New Member

    Hi

    I would like to create a stored procedure that accepts the current date as a parameter.

    something like:

    create procedure proc_name
    @rundate datetime = getdate
    as
    .
    .
    .
    .
    go

    and then run it with

    execute proc_name

    However, doing it this way I get 'syntax error converting datetime from character string.

    Can anyone help / point me in the right direction please.

    PS
    If I don't specify a default, and then put the parameter at the end of the execute statement as below it works fine, but this kind of defeats the object of automating the procedure in the first place!

    create procedure proc_name
    @rundate datetime
    as
    .
    .
    .
    .
    go

    execute proc_name '2005-12-20'
  2. Adriaan New Member

    Unless your database is accessed from different time zones, I can't see why you would need to feed the current date to SQL Server - it is aware of the current date, so no need to pass it as a parameter.

    Why not use

    create procedure proc_name
    (@rundate datetime)
    as

    SET @rundate = ISNULL(@rundate, GETDATE())

    .....

    GO


    You can use CONVERT(VARCHAR(10), GETDATE(), 120) to drop the time part, if you're only interested in the date.
  3. SQLDBcontrol New Member

    Try this:

    create procedure proc_name
    @rundate datetime = null
    as

    if @rundate is null set @rundate = getdate()

    .
    .
    .
    .


    quote:Originally posted by MooreCock

    Hi

    I would like to create a stored procedure that accepts the current date as a parameter.

    something like:

    create procedure proc_name
    @rundate datetime = getdate
    as
    .
    .
    .
    .
    go

    and then run it with

    execute proc_name

    However, doing it this way I get 'syntax error converting datetime from character string.

    Can anyone help / point me in the right direction please.

    PS
    If I don't specify a default, and then put the parameter at the end of the execute statement as below it works fine, but this kind of defeats the object of automating the procedure in the first place!

    create procedure proc_name
    @rundate datetime
    as
    .
    .
    .
    .
    go

    execute proc_name '2005-12-20'


    Karl Grambow

    www.sqldbcontrol.com
  4. FrankKalis Moderator

    You cannot use a function like GETDATE() as default for a parameter of a stored procedure.
    Alternatively to Adriaan's suggestion you can use


    ALTER PROCEDURE proc_name
    @rundate datetime = NULL
    AS
    IF @rundate IS NULL
    SELECT GETDATE()
    ELSE
    SELECT @rundate
    GO

    ...



    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  5. ranjitjain New Member

    ALTER PROCEDURE proc_name @rundate datetime = NULL
    AS
    select ISNULL(@rundate,getdate())
  6. Madhivanan Moderator

    Also

    Create procedure proc_name
    @rundate datetime
    as
    select @rundate as Today
    GO

    When executing the sp, you need to use DateTime variable to pass value to parameter

    Declare @date DateTime
    set @date =getdate()
    Exec proc_name @date



    Madhivanan

    Failing to plan is Planning to fail
  7. MooreCock New Member

    Thanks for all the replies.<br /><br />What I omitted, that some of you picked up on, was that what I really wanted to do was pass a date to the procedure but use the current date as default if I didn't put a date in. I'll try your suggestions tomorrow.<br />[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  8. mmarovic Active Member

    With approach mention there is a possibility to be affected by parameter sniffing. Better make sure date is always passed when stored procedure is executed. I think the sp caller can pass current date if it is needed.

Share This Page