Date in String doesn't work

Can anyone please tell me why this query works:

DECLARE @.valuation_date smalldatetime, @.non_admit_date smalldatetime
SET @.valuation_date = '2006-03-31'
SET @.non_admit_date = DATEADD(dd, -90, @.valuation_date)
SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= @.non_admit_date

And this one does not:

DECLARE @.valuation_date smalldatetime, @.non_admit_date smalldatetime, @.arc_sql nvarchar(4000)
SET @.valuation_date = '2006-03-31'
SET @.non_admit_date = DATEADD(dd, -90, @.valuation_date)
SET @.arc_sql = 'INSERT INTO Results SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= ' @.non_admit_date
SELECT @.arc_sql
exec sp_executesql @.arc_sql

The second one gives me this error: "Syntax error converting character string to smalldatetime data type."

How can I run the second one? I need to dynamically build an Insert statement and this is stopping me.

Thanks!

-Ticia

DECLARE @.nondate DATETIME

DECLARE @.arc_sql NVARCHAR(200)

SET @.nondate = DATEADD(dd, -90, GETDATE())

SET @.arc_sql = N'SELECT @.non_admit_date '

EXECUTE sp_executesql @.arc_sql, N'@.non_admit_date DATETIME', @.non_admit_date = @.nondate

I had to modify the code to make sure i got it working but I'll point out the 2 mistakes you were making.

SET @.arc_sql = 'INSERT INTO Results SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= ' @.non_admit_date

This is completely incorrect because you can't have a string and a variable like that

The proper version of this is

SET @.arc_sql = 'INSERT INTO Results SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= @.non_admit_date '

Next

exec sp_executesql @.arc_sql

The proper version for this is

EXECUTE sp_executesql @.arc_sql, N'@.non_admit_date DATETIME', @.non_admit_date = @.nondate

Now the only thing that I had to do was i had to rename the actual variable to something else other then the parameter which upon later reflection I see is not necessary so you can change @.nondate to @.non_admit_date if you want. I just found it confusing and to eliminate problems i made sure i wasn't getting errors fromit.

hope this helps

|||

You can also use the below code to make it dynamic SQL.

DECLARE @.valuation_date smalldatetime, @.non_admit_date smalldatetime, @.arc_sql nvarchar(4000)
SET @.valuation_date = '2006-03-31'

SET @.non_admit_date = DATEADD(dd, -90, @.valuation_date)
SET @.arc_sql = 'INSERT INTO Results SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= cast(''' + cast(@.non_admit_date as varchar(50)) + ''' as smalldatetime)'
exec sp_executesql @.arc_sql

|||

Ticia, This should work for you; just a little miner change in code!

DECLARE @.valuation_date smalldatetime

DECLARE @.non_admit_date smalldatetime

DECLARE @.arc_sql nvarchar(4000)

Declare @.SQL_String nvarchar(4000)

SET @.valuation_date = '2006-03-31'

SET @.non_admit_date = DATEADD(dd, -90, @.valuation_date)

SET @.arc_sql = 'INSERT INTO Results SELECT SUM(Premium) FROM Reserves WHERE Reserve_Paid_to_Date >= '

SET @.SQL_String = @.arc_sql + @.non_admit_date

exec sp_executesql @.SQL_String