Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Working with datetime with dynamic query in SQL Server

I am using a dynamic query wherein I want to use the variable which holds the datetime, whenever I execute the query it says cannot convert datetime from string, when I cast that variable to varchar(max), it takes it as string and not datetime, so how should I execute the query..

Below is my SQL query which 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 are the datetime type coming from different temp table. and stored in these variable..

How should I execute this query?

like image 790
Abbas Avatar asked Feb 17 '11 11:02

Abbas


People also ask

How do I run a dynamic SQL query in SQL Server?

Executing dynamic SQL using sp_executesql sp_executesql is an extended stored procedure that can be used to execute dynamic SQL statements in SQL Server. we need to pass the SQL statement and definition of the parameters used in the SQL statement and finally set the values to the parameters used in the query.

Can I use CTE in dynamic SQL?

Using CTEs, for instance, you can use SELECT from <subquery> in Open SQL. In my case I needed to execute dynamic SELECT count( DISTINCT col1, col2, …) which is not possible in the regular OpenSQL.


1 Answers

You need to quote 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) + ''''
like image 171
bleeeah Avatar answered Oct 21 '22 15:10

bleeeah