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)

CHARINDEX() in SQL Server with example

 This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

expressionToFind
A character expression containing the sequence to find. Max length 8000 bytes

expressionToSearch
A character expression to search.

start_location
An integer or bigint expression at which the search starts. If start_location is not specified, has a negative value, or has a zero (0) value, the search starts at the beginning of expressionToSearch. This is an optional parameter

Note

• The first position in string is 1.
• If the expressionToFind is not found in expressionToSearch, the CHARINDEX function will return 0.
• Search is not case sensitive

Example:

1) SELECT CHARINDEX('TO', 'Customer') AS MatchPosition;

Result: 4

(Substring is not case sensitive)

2) SELECT CHARINDEX('mer', 'Customer', 3) AS MatchPosition;

Result: 6

3) SELECT CHARINDEX('o', 'Rohit Techvlog', 3) AS MatchPosition;

Result: 13

(Since it started from 3rd position it returns the position of 2nd 'o')

4) SELECT CHARINDEX('z', 'Rohit Techvlog', 1) AS MatchPosition;

Result: 0

(No match found)

5) This function can be used in CASE statement

DECLARE @Name AS VARCHAR(100)= 'Find SQL articles on Rohit techvlog';

SELECT CASE

          WHEN CHARINDEX('rohit', @Name) > 0

           THEN 'Exists'

           ELSE 'Not Exists'

       END AS FindSubString;

Result: Exists

6) This function can be used in a output of existing data in a table.

select JobTitle,CHARINDEX('p', JobTitle) as position_of_p

from Employee

Result: