Surround Variable with Single Quotes when Passing SQL Smalldatetime

Question

I have a problem/question that I may have been staring at too long today.

I have a stored procedure that receives data from an web application. The data comes in as smalldate time format. I am trying to pass this information to a second stored procedure but the second one won't fire unless the data is in single quotes. Would it be better to cast this as varchar?

The SET @CompletedDate must be '2010-01-20 15:28:00" for obvious reasons. How do I pass this information to the second procedure?

DECLARE @return_value int
    ,@TaskID int
    ,@CompletedDate smalldatetime
SET @TaskID = 90
SET @CompletedDate = 2010-01-20 15:28:00

EXEC    @return_value = [dbo].[usp_Task_Completion]
        @TaskID = @TaskID,
        @CompletedDate = @CompletedDate

Here is the usp_Task_Completion SP

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Task_Completion]
@TaskID int 
,@CompletedDate smalldatetime
AS
BEGIN

SET NOCOUNT ON;

--Mark Transaction as complete
UPDATE dbo.Task
SET Completed = 1
    ,CompletedDate =  @CompletedDate
WHERE TaskID = @TaskID

Answer 1

no because a varchar will also have to be enclosed in single quotes, just put quotes around it

DECLARE @return_value int
    ,@TaskID int
    ,@CompletedDate smalldatetime
SET @TaskID = 90
SET @CompletedDate = '2010-01-20 15:28:00'

EXEC    @return_value = [dbo].[usp_Task_Completion]
        @TaskID = @TaskID,
        @CompletedDate = @CompletedDate

Answer 2

yes, you have to enclose that small datetime 2010-01-20 15:28:00 into single quotes -> '2010-01-20 15:28:00'

Answer 3

You probably have a naming confict on @CompletedDate. Change the variable name in the declare, and then change your set command to use single quotes, like this:

SET @CompletedDateNewName = '2010-01-20 15:28:00'

@CompletedDate can still be a smalldatetime type

Answer 4

This is a little much for a comment. What's the problem? Below are my tests:

/*
create proc usp_Task_Completion (
    @TaskID int,
    @CompletedDate smalldatetime)
as 

    return 1000
*/
go


DECLARE @return_value int
    ,@TaskID int
    ,@CompletedDate smalldatetime
SET @TaskID = 90

/* This works */
--SET @CompletedDate = '2010-01-20 15:28:00'    

/* This doesn't work (incorrect syntax near '15'. */
--SET @CompletedDate = 2010-01-20 15:28:00      

/* This runs but yields unintended results (2010-01-20 = 1989; convert to datetime = '1905-06-13' */
SET @CompletedDate = 2010-01-20 

EXEC    @return_value = [dbo].[usp_Task_Completion]
        @TaskID = @TaskID,
        @CompletedDate = @CompletedDate

select @return_value

Answer 5

I'm guessing the CompletedDate column isn't smalldatetime, and it's trying to cast every value in that column into the smalldatetime type (with some failing). Please check the column type to be really sure.