Convert date to string in concatenated string

Having trouble converting @StartDate and @EndDate to the proper string in the concatenated string below, #1. I keep getting the following error:
"Conversion failed when converting date and/or time from character string."

#1
Declare @StartDate datetime,
@EndDate datetime,
@STRSQL As varchar(5000)

Set @StartDate='01/01/2015'
Set @EndDate='08/31/2015'

Set @STRSQL = @STRSQL + ' Select CusName from custable where p.Paid_DATE between Convert(varchar(10), ' + @StartDate + ',101) and Convert(varchar(10), ' + @EndDate + ',101)'
Execute(@STRSQL)

I have tried omitting Convert and still get the error message:
"Conversion failed when converting date and/or time from character string."

#2
When I do this it works
Declare @StartDate varchar(50),
@EndDate varchar(50),
@STRSQL As varchar(5000)

Set @StartDate='01/01/2015'
Set @EndDate='08/31/2015'

Set @STRSQL = @STRSQL + ' Select CusName from custable where p.Paid_DATE between ' + @StartDate + ' and ' + @EndDate + ''
Execute(@STRSQL)

#3
Without a concatenated string I can do this and get data:
Declare @StartDate datetime,
@EndDate datetime,

Set @StartDate='01/01/2015'
Set @EndDate='08/31/2015'

Select CusName from custable where p.Paid_DATE Between @StartDate and @EndDate

What do I need to do in #1 in the concatenated string to get results?

Since I modified the original select statement here to shorten the string I may have a misplace tick mark here but not in my original string meaning that what is here is not throwing an error due to that kind of syntax error.

TIA

Do the conversion before concatenating to the string. Also, a) you need to initialize @STRSQL, b) you need the date strings to be in single quotes, c) assuming p.Paid_DATE refers to the Paid_DATE column in custable, you need to alias the custable. So, like shown below.

Declare @StartDate datetime, 
@EndDate datetime,
@STRSQL As varchar(5000) = ''

Set @StartDate='01/01/2015'
Set @EndDate='08/31/2015'

Set @STRSQL = @STRSQL +
	 ' Select CusName from custable AS p where p.Paid_DATE between ''' 
	 + Convert(varchar(10),  @StartDate ,101)
	 + ''' and '''
	 + Convert(varchar(10), @EndDate , 101)
	 + ''''
Execute(@STRSQL)

You cannot directly add (concatenate) a date to a string. Since date has a higher data precedence, SQL must convert the other string to a date first, and obviously that fails. This shows the main disadvantage of "overloading" the + to be both add and concatenation.

The second one works only because SQL is able to "short-circuit": it never attempts to concat @startdate to the string because the result of the expression is already known to be NULL because @STRSQL is null.

You need to explicitly convert the dates to strings to concat them to a string. Also, you should always use format YYYYMMDD, because that format always translates correctly, whereas mm/dd/yyyy can be misinterpreted/be invalid depending on date and language settings.

Thus:

Declare @StartDate datetime, 
@EndDate datetime,
@STRSQL As varchar(5000)

Set @StartDate='01/01/2015'
Set @EndDate='08/31/2015'

Set @STRSQL = ''
Set @STRSQL = @STRSQL + ' Select CusName from custable where p.Paid_DATE between ''' +
    Convert(varchar(8), @StartDate,112) + ''' and ''' + Convert(varchar(8), @EndDate,112) + ''''
Print @STRSQL
Exec(@STRSQL)
1 Like

Thanks Scott. Putting the Convert inside the ticks was the key