0

I want to pass the system date (GETDATE) only as a parameter in my SQL stored procedure. But I am getting an error while executing the procedure.

SQL query:

  ALTER PROCEDURE ST_PRO_GETUSER
  @D DATETIME = GETDATE  --// passing GETDATE as a parameter.
  AS BEGIN
    select case when branch in ('A25','B10','C10')  
    then 'BR B1' Else 'BR B2' 
    end As [COLLECTION],FIXDATE 
    from MAIN_COUNTER where TDATE=@D  --//Just want to pass date only
    group by COLLECTION,FIXDATE 
 END

 EXEC KK_SP_GETUSER_DIV

Error:

Conversion failed when converting date and/or time from character string.

What I have to do for it?

Improve this question
3
  • A similar question. Commented Oct 19, 2020 at 7:15
  • You're not passing it as a parameter - you're using it as a default for when no parameter is passed. Commented Oct 19, 2020 at 7:16
  • Do u have any solution.. how to pass getdate as a parameter? Commented Oct 19, 2020 at 7:22

3 Answers 3

Reset to default
1

To pass as a parameter you just declare a variable and pass it in:

DECLARE @DATE DATETIME = GETDATE();
EXEC ST_PRO_GETUSER @DATE;

And if you want the date only, change the datatype of your parameter to a date and then do:

DECLARE @DATE DATE = GETDATE();
EXEC ST_PRO_GETUSER @DATE;

But part of your question seems to actually be asking how to specify a default parameter value. You can't use a function for the default value, so instead do:

CREATE PROCEDURE ST_PRO_GETUSER
(
    @Date DATETIME = null
    -- Change to DATE datatype if you don't want a time component.
    -- @Date DATE = null
)
AS
BEGIN
    SET NOCOUNT ON;

    -- Default the @Date here is its null.
    -- Note this doesn't handle the case when the caller wants to pass in null.
    SET @Date = COALESCE(@Date,GETDATE());

    -- SP Body

    RETURN 0;
END
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

-1

Solved My Self

     ALTER PROCEDURE ST_PRO_GETUSER
     @Date datetime = null
     as
     IF @Date is null
     SET @Date = getdate()  --// passing GETDATE as a parameter.
      BEGIN
       select case when branch in ('A25','B10','C10')  
       then 'BR B1' Else 'BR B2' 
       end As [COLLECTION],FIXDATE 
       from MAIN_COUNTER where TDATE=@D  --//Just want to pass date only
       group by COLLECTION,FIXDATE 
     END

EXEC ST_PRO_GETUSER
Improve this answer

2 Comments

Thats not passing a parameter, thats handling no parameter being passed.
are you saying that your stored procedure is working now ? No syntax error ?
-2

GETDATE() is the correct syntax, not GETDATE.

Improve this answer

1 Comment

when i use GETDATE() i get error: Must declare the scalar variable "@D".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.