Friday, September 25, 2020

error while passing Datetime parameter in dynamic sql command

 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)