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
Lets see using below example:
Declare @OrderDate Datetime
Declare @sql varchar(max)
set @OrderDate='2010-12-29'
set @sql='Select SalesAmount,taxamt,totalproductcost,orderdate from
[dbo].[FactInternetSales]
where
orderdate='''+@OrderDate+''''
exec (@sql)
Output:
Msg 241, Level 16, State 1, Line 8
Conversion failed when converting date and/or time from character string.
To resolve this we need to convert datetime variable. It looks like below.
Declare @OrderDate Datetime
Declare @sql varchar(max)
set @OrderDate='2010-12-29'
set @sql='Select SalesAmount,taxamt,totalproductcost,orderdate from
[dbo].[FactInternetSales]
where
orderdate='''+CONVERT(VARCHAR(10),@OrderDate, 101)+''''
exec (@sql)
