CODEWITHSUNDEEP
sql-serversql-server-2005
user603007
user603007

Reputation: 11794

Error converting data type nvarchar to datetime?

I am using sql server 2005 and when I call my stored proc i am getting this error:

Error converting data type nvarchar to datetime. So how can I pass in my datetime to run my stored proc?

create procedure [dbo].[myProc]
@id as int,
@date as datetime
as
begin
    select id , DATEPART(day, @date)
    from myTable
    where convert(varchar,[date],101) =  convert(varchar,@date,101)
    and id =@id
end

DECLARE @return_value int

EXEC    @return_value = [dbo].[myProc]
        @id = 1,
        @date = N'getdate()'

SELECT  'Return Value' = @return_value

GO

Upvotes: 0

Views: 5836

Answers (1)

Aaron Bertrand
Aaron Bertrand

Reputation: 280262

You should be using an open-ended date range instead of converting both sides of the equation to a regional string.

ALTER PROCEDURE [dbo].[myProc]
  @id   INT,
  @date DATETIME
AS
BEGIN
  SET NOCOUNT ON;

  SELECT id, DATEPART(DAY, @date)
    FROM dbo.myTable
    WHERE id = @id
    AND [date] >= DATEADD(DAY, 0, DATEDIFF(DAY, 0, @date))
    AND [date] <  DATEADD(DAY, 1, DATEDIFF(DAY, 0, @date));
END
GO

Now, you can't just pass getdate() into a stored procedure call like that (the syntax is invalid), and you certainly can't pass it as a string. So a couple of options.

(a) Declare a variable up front, assign the current date/time to the variable, and pass the variable in:

DECLARE @d DATETIME;
SET @d = GETDATE();
EXEC [dbo].[myProc] @id = 1, @date = @d;

(b) Make the parameter optional, and then don't bother passing that param in:

ALTER PROCEDURE [dbo].[myProc]
  @id   INT,
  @date DATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SET @date = DATEADD(DAY, 0, DATEDIFF(DAY, 0, 
    COALESCE(@date, GETDATE())));

  SELECT id, DATEPART(DAY, @date)
    FROM dbo.myTable
    WHERE id = @id
    AND [date] >= @date
    AND [date] <  DATEADD(DAY, 1, @date);
END
GO

Now your call can just be:

EXEC dbo.myProc @id = 1;

Also, you shouldn't use 'single quotes' for column aliases - this syntax is deprecated. Use [square brackets] instead.

Upvotes: 2

Related Questions