SQLTeam.com | Weblogs | Forums

Convert date to string in concatenated string


#1

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


#2

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)

#3

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)

#4

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