Working with datetime with dynamic query in SQL Server

Question

Hey guys, I am using a dynamic query in which I want to use a variable that contains datetime whenever I execute a query that it says cannot convert datetime from a string when I pass this variable to varchar(max) , it takes it as a string, not a datetime , since I have to fulfill the request.

Below is my SQL query that I am trying to execute.

 SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM ' +@TABLE +' B WHERE B.TillDate BETWEEN CONVERT(VARCHAR(10),' +@FROMDATE +', 101) and CONVERT(VARCHAR(10), DATEADD(DD,1,' +@TODATE +'), 101)' EXEC SP_EXECUTESQL @SQL1 

here @fromdate and @todate is a datetime type coming from another temp table. and stored in these variables ..

Now how do I fulfill this request. Any suggestions. answer soon ...

Thank you and welcome abbas electricwala.

Answer 1

You need to indicate your dates.

 SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM ' +@TABLE +' B WHERE B.TillDate BETWEEN ''' + CONVERT(VARCHAR(10),@FROMDATE, 101) + ''' and ''' + CONVERT(VARCHAR(10),DATEADD(DD,1,@TODATE), 101) + '''' 

Answer 2

You should not concatenate parameter values ​​like this. The best solution is to use a parameterized query with sp_executesql.

 DECLARE @sql nvarchar(4000) select @sql = N' SELECT B.FacId , B.FacName , B.BookCode , B.BookName , B.Quantity , CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM ' + quotename(@TABLE) + N' B WHERE B.TillDate BETWEEN cast(floor(cast(@fromDate as float)) as datetime) AND cast(floor(cast(@toDate as float)) as datetime)' EXEC sp_executesql @sql, N'@fromDate datetime, @toDate datetime', @FROMDATE, @TODATE 

It should be noted that sp_executesql:

• Parameters NVARCHAR values
• The third and fourth parameters retain their original data type and do not need to be converted to varchar. This again protects the SQL injection and makes the query more readable, since you forbid the soup quotation, which is so common in Dynamic SQL

Some additional changes were added to the request:

• The table name is wrapped in the QUOTENAME() function, which protects against SQL injection of the object name
• How the date part of the date and time variables is deleted is not very optimal. Performing convert(,,101) is an expensive operation that can be better done using casting to swim and fill this value.

Answer 3

I would like to see your variable definitions, but I suspect because @FROMDATE and @TODATE are datetime, and you use them in the string concatenation statement. So you can fix this:

 SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM ' +@TABLE +' B WHERE B.TillDate BETWEEN CONVERT(VARCHAR(10),'+CAST(@FROMDATE as varchar(15))+', 101) and CONVERT(VARCHAR(10), DATEADD(DD,1,'+CAST(@TODATE as varchar(15))+'), 101)' 

However, the best solutions are:

• Don't use dynamic SQL at all, maybe @TABLE is not that different, and you can combine them in a form or something.
• Pass parameters directly to sp_executeSQL and thus save their types, for example.

SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, B.TillDate AS TILLDATE FROM ' +@TABLE +' B WHERE B.TillDate BETWEEN @inFROMDATE and @inTODATE'

EXEC SP_EXECUTESQL @SQL1,'@inFROMDATE datetime, @inTODATE',@inFromDate = @FROMDATE, @inTODATE = @TODate

Answer 4

I think this might work:

  DECLARE @tempdate datetime SET tempdate =DATEADD(DD,1,@TODATE) SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity,'''+ cast (B.TillDate as VARCHAR(50))+''' AS TILLDATE FROM ' +@TABLE +' B WHERE B.TillDate BETWEEN '''+cast(@FROMDATE as VARCHAR(50))+''' and '''+cast(@tempdate as VARCHAR(50))''' EXEC SP_EXECUTESQL @SQL1 

Answer 5

Try the following:

 declare @sql1 varchar(max) declare @table sysname declare @FROMDATE datetime declare @TODATE datetime set @table = 'MyTable' set @FROMDATE = GETDATE() set @ToDATE = GETDATE() SET @SQL1 = 'SELECT B.FacId, B.FacName, B.BookCode, B.BookName, B.Quantity, CONVERT(VARCHAR(10), B.TillDate, 104) AS TILLDATE FROM ' +@TABLE +' B WHERE B.TillDate BETWEEN CONVERT(Datetime,''' + CONVERT(VARCHAR(10),@FROMDATE, 101) + ''', 101) and CONVERT(DATETIME,'''+ CONVERT(VARCHAR(10), DATEADD(DD,1,@TODATE), 101) + ''', 101)' print @sql1 

But look at Joel Mansford's answer to avoid double conversion.

Answer 6

Late, but maybe it will help someone

What you need is quotes around your date, you have already received your answer.

Below is an example of what I usually add to my request

 '(CONVERT(DATETIME,CONVERT(varchar,gd.CreatedDate),106) <= CONVERT(DATETIME,'''+CONVERT(varchar, @EndDate ) +''',106))' 

note that @EndDate is of type Datetime here