• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 741
  • Last Modified:

Dynamic SQL with DateTime conversion

Having to convert a text string to datetime.  Once converted I'm comparing the new datetime variable to a real datetime in the database.  This works fine in regular SQL, but when I convert it to dynamic SQL I get the following error message:   "Syntax error converting datetime from character string."

I'm not especially strong with dynamic sql, so any assistance is appreciated.
DECLARE @TextMonthYear datetime
DECLARE @MyStatement varchar(8000)
 
SET @TextMonthYear = Dateadd(Month,1,CONVERT(datetime, '10' + '/1/' + '2006'))
 
SET @MyStatement = n'	SELECT 	*
			FROM 	TableName
			WHERE	TableName.DateField < '+@TextMonthYear+'' 
EXEC (@MyStatement)

Open in new window

0
jobprojn
Asked:
jobprojn
  • 4
  • 4
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
do you REALLY need dynamic sql?

if not:
DECLARE @TextMonthYear datetime
DECLARE @MyStatement varchar(8000)
 
SET @TextMonthYear = Dateadd(Month,1,CONVERT(datetime, '10' + '/1/' + '2006'))
 
SELECT       *
FROM       TableName
WHERE      TableName.DateField < @TextMonthYear  

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
if yes:
DECLARE @TextMonthYear datetime
DECLARE @MyStatement varchar(8000)
 
SET @TextMonthYear = Dateadd(Month,1,CONVERT(datetime, '10' + '/1/' + '2006'))
 
SET @MyStatement = n'      SELECT       *
                  FROM       TableName
                  WHERE      TableName.DateField < @dt ' 
EXEC sp_executesql @MyStatement, N'@dt datetime', @TextMonthYear 

Open in new window

0
 
jobprojnAuthor Commented:
angell, unfortuately yes, I have to deal wtih dynamic sql.  I attempted your solution and get the following error message:  Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Suggestions??
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry:
DECLARE @TextMonthYear datetime
DECLARE @MyStatement nvarchar(8000)
 
SET @TextMonthYear = Dateadd(Month,1,CONVERT(datetime, '10' + '/1/' + '2006'))
 
SET @MyStatement = n'      SELECT       *
                  FROM       TableName
                  WHERE      TableName.DateField < @dt ' 
EXEC sp_executesql @MyStatement, N'@dt datetime', @TextMonthYear 

Open in new window

0
 
David ToddSenior DBACommented:
Hi,

I've corrected Angell's script ...

Try now.

Cheers
  David
DECLARE @TextMonthYear datetime
DECLARE @MyStatement Nvarchar(8000)
 
SET @TextMonthYear = Dateadd(Month,1,CONVERT(datetime, '10' + '/1/' + '2006'))
 
SET @MyStatement = n'      SELECT       *
                  FROM       TableName
                  WHERE      TableName.DateField < @dt ' 
EXEC sp_executesql @MyStatement, N'@dt datetime', @TextMonthYear 

Open in new window

0
 
jobprojnAuthor Commented:
angellll and dtodd, it appears all three dynamic sql scripts are identical....... am I missing something?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes:
DECLARE @MyStatement Nvarchar(8000)

instead of:
DECLARE @MyStatement varchar(8000)

0
 
jobprojnAuthor Commented:
You are the man!!  Thank you.
0
 
jobprojnAuthor Commented:
Angellll's solution worked.  Dynamic sql is my nemesis, don't make it yours too.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now