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?
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.
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.
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) + ''''
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With