CODEWITHSUNDEEP
sqlsql-server-2005
Benk
Benk

Reputation: 1312

comparing datetime variables

How do I compare datetime variable passed to the store procedure with datetime variable in the table.

e.g. In my where clause @paramDate value is 2/10/2012

 set sql = 'WHERE product.RegisteredDate >= ' + @paramDate

when I exec(@sql)

it fails, Error: 

 Conversion failed when converting datetime from character string.

Thx

Upvotes: 0

Views: 2119

Answers (3)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

SET @sql = '... WHERE product.RegisteredDate >= ''' 
    + CONVERT(CHAR(8), @paramDate, 112) + ''';';

If @ParamDate is NULL you can probably do this:

SET @sql = 'SELECT ...';

SET @sql = @sql + COALESCE(' WHERE product.RegisteredDate >= '''
    + CONVERT(CHAR(8), @paramDate, 112) + ''';', '');

Or even:

SET @sql = 'SELECT ...';

IF @paramDate IS NOT NULL
BEGIN
    SET @sql = @sql + '... WHERE product.RegisteredDate >= ''' 
        + CONVERT(CHAR(8), @paramDate, 112) + ''';';
END

Upvotes: 3

Mike
Mike

Reputation: 773

Since you're trying to execute a dynamic query, you could use sp_executesql instead of exec so that you can use parameters in your generated query. Here are the details for sp_executesql.

For example:

set @sql = 'WHERE product.RegisteredDate >= @dynamicParm'

EXECUTE sp_executesql @sql, N'@dynamicParm DATETIME', @dynamicParm = @paramDate

Upvotes: 0

David Faber
David Faber

Reputation: 12486

Try this:

CONVERT(varchar, @paramDate, 101)

See here for more information: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Upvotes: 2

Related Questions