quote:

---

Originally posted by NaughtyZute

Local variables:
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SET @StartDate = '2012-10-01'
SET @EndDate = '2013-02-01'

String concatention:
SET @strColumns='SELECT * FROM ' + @strTableName + ' WHERE ' + @strColumns + ' AND LOAD_DATE BETWEEN ' + @StartDate + ' AND ' + @EndDate

Error Message: Msg 241... Syntax error converting datetime from character string...

I've been away from SQL for awhile and not sure I've ever used SQL 2005. Thanks for any help provided. I suspect my problem is somewhere in those pesky single quotes.


Zute
<h5>p.s. If you don't like your life; change the way you're living.

---

quote:

---

Originally posted by jeffw8713

This is really not a good idea - you should not be building (concatenating) SQL like this as it opens you to a SQL injection attack. With that said, if you really want to do this - then you need to declare the variables as a character data type and use convert to specify the string format of the date you want.

I would use CHAR(8) and CONVERT(char(8), datevalue, 121) which will return the date in the format YYYYMMDD.

I would also recommend that you reconsider using BETWEEN for the date comparison. The column LOAD_DATE is a datetime data type - which includes the time portion and using BETWEEN which is inclusive will either include data you don't want - or exclude data you do want. For example:

BETWEEN '20120101' AND '20130201' will include anything where the LOAD_DATE is exactly '2013-02-01 00:00:00.000' but will not include anything greater than or equal to '2013-02-01 00:00:00.333'.

---