dynamic sql not making the right where clause statement

Hi,
I'm still working on converting the query to dynamic SQL.  The DB is SQL Server 2005.  In this particular part of the query there are two parameters passed in for the begining and end dates.  Below is the statement that worked.  This part is being converted simply because  some other parts of the code require it.

Original code that worked:

AND cast(CASE ISDATE(RTRIM(LTRIM(MAT1_01_07))) WHEN 0 THEN '1/1/1901' ELSE RTRIM(LTRIM(MAT1_01_07))

END as datetime) BETWEEN  @StatusDateBegin AND @StatusDateEnd

--------------------------
Below is the dynamic conversion code of above:

-------------------------Status Date Solution------------------
SET @StatusDates = ' AND cast(CASE ISDATE(RTRIM(LTRIM(StatusDate))) '  
      +  ' WHEN 0 THEN ' + '''1/1/1901'''         
      + ' ELSE RTRIM(LTRIM(StatusDate)) END as datetime) '
      + ' BETWEEN ' + @StatusDateBegin + ' AND ' + @StatusDateEnd
SET @SQL_WHERE = @SQL_WHERE + @StatusDates

--------------------
Below is the actual part of the where clause that is built. This is should be much more complete:

AND cast(CASE


Thanks,
FairSkiesAsked:
Who is Participating?
 
imitchieConnect With a Mentor Commented:
SET @StatusDates = ' AND cast(CASE ISDATE(RTRIM(LTRIM(StatusDate))) '  
      +  ' WHEN 0 THEN ' + '''1/1/1901'''        
      + ' ELSE RTRIM(LTRIM(StatusDate)) END as datetime) '
      + ' BETWEEN ''' + convert(varchar, @StatusDateBegin, 102) + ''' AND ''' + convert(varchar, @StatusDateEnd, 102) + ''''
SET @SQL_WHERE = @SQL_WHERE + @StatusDates
0
 
imitchieCommented:
I assume @StatusDateEnd and @StatusDateEnd are datetimes
0
 
awking00Commented:
I also assume that StatusDate is also a datetime, so it won't look anything like the "MAT1_01_07" string that worked. I think you need to cast it as a character string before applying the trim functions to it (and may also require some further manipulation if it needs to look like MAT1_01_07).
0
 
FairSkiesAuthor Commented:
Hi,
Thanks for the help.  StatusDate is datetime in the view and the input parameters @StatusDateEnd are @ServiceDateBegin are are both datetime.
0
 
FairSkiesAuthor Commented:
I am still a little confused about the escaping the '.  It doesn't quite seem consistient to me
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.