Solved

Dynamic SQL with DateTime conversion

Posted on 2008-10-09
9
695 Views
Last Modified: 2012-05-05
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
Comment
Question by:jobprojn
  • 4
  • 4
9 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22682866
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22682873
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
 

Author Comment

by:jobprojn
ID: 22682961
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22682991
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 35

Expert Comment

by:David Todd
ID: 22682993
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
 

Author Comment

by:jobprojn
ID: 22683101
angellll and dtodd, it appears all three dynamic sql scripts are identical....... am I missing something?
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 22683106
yes:
DECLARE @MyStatement Nvarchar(8000)

instead of:
DECLARE @MyStatement varchar(8000)

0
 

Author Closing Comment

by:jobprojn
ID: 31504851
You are the man!!  Thank you.
0
 

Author Comment

by:jobprojn
ID: 22683139
Angellll's solution worked.  Dynamic sql is my nemesis, don't make it yours too.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now