Procedure failed to run shows error: Conversion failed when converting date and/or time from character string

Question

Hi, I have written a simple procedure:

CREATE PROCEDURE [dbo].[sp_GetPublishedDocs2] @FromDate as Datetime AS BEGIN

DECLARE @strSQL VARCHAR(5000)

SET @strSQL='SELECT * From Task WHERE 1=1 '

IF @FromDate <>'1/1/1900'

SET @strSQL = @strSQL + ' AND Task.CreatedDate >= '+Cast(@FromDate as Datetime)

EXEC(@strSQL)

END

It run successfully when I pass parameter '1/1/1900' however when I pass any other date it says: Conversion failed when converting date and/or time from character string.

Is there anyone.. who could help me...

Thanks in Advance.

Answer 1

You should cast the @FromDate into a varchar since you are doing a string concat.

CREATE PROCEDURE [dbo].[sp_GetPublishedDocs2]
( 
    @FromDate as Datetime
)
AS 
BEGIN

    DECLARE @strSQL VARCHAR(5000)

    SET @strSQL = 'SELECT * From Task WHERE 1=1 '

    IF @FromDate <>'1/1/1900'
    BEGIN
        SET @strSQL = @strSQL + ' AND Task.CreatedDate >= ''' + Cast(@FromDate as varchar) + ''''
    END

    EXEC(@strSQL)

END

Try to avoid SELECT * FROM. It is faster to define all columns explicitly.

HINT: for testing you could use PRINT favor of EXEC to see what sql has been produced.

EDIT: You might use VARCHAR(MAX) here...

Answer 2

1. Don't use the sp_ prefix for stored procedures, because it's used for system procedures
2. If you didn't already, search this site or Google for "dynamic search conditions" in TSQL
3. If you didn't already, search this site or Google for "dynamic SQL"
4. The format YYYYMMDD for date string literals is always interpreted correctly
5. Don't use SELECT * unless you have a good reason
6. Always include the schema in object names
7. The '2' at the end of your procedure name is a code smell that suggests you may not be handling source code versioning and deployment cleanly

And last but not least, keep it simple if you can:

CREATE PROCEDURE [dbo].[usp_GetPublishedDocs2] 
(  
    @FromDate as Datetime 
) 
AS  
BEGIN 
    if @FromDate = '19000101'
    begin
        select * from dbo.Task
    end
    else
    begin
        select * from dbo.Task where CreatedDate >= @FromDate
    end
END 

A bit of research into dynamic search conditions will show you other possible ways to implement this, which will be useful if you need to have more complex search conditions.