SQL statement, Conversion failed when converting datetime from character string. Error resolution

In the project development process, we often have to do some time-based queries, such as querying the history of the specified time range, but these times are passed from the UI parameters, so we write the SQL statement must use the string concatenation. Of course, writing SQL statements in C # is a good deal, and you can use C # String functions to do the corresponding data type conversions. However, if you use a stored procedure, it is a bit tangled. Here's a look at some of the problems I encountered in writing the stored procedure:

To explain the problem more directly, write the following simple example:

Declare @dateFrom datetime;Declare @dateTo   datetime;Declare @str nvarchar( -);Declare @strOne nvarchar( -);Declare @strTwo nvarchar( $);Declare @sql nvarchar( +);Set @dateFrom = '2014-01-01';Set @dateTo = getdate();Set @strOne = 'and DateCreated >=' + @dateFrom;Set @str = 'SELECT * from Users where 1 = 1';Set @sql = @str + @strOne;Print @sql;execsp_executesql@sql

At this point, if you execute SQL, the following error will occur:

The reason is analyzed mainly because the @datefrom data type is datetime, and the type does not match when the string is concatenated, so the following is the type conversion:

There is a convert function in SQL Server that you can use to do type conversions, using the following:

Definition and usage

The CONVERT () function is a general function that converts a date to a new data type.

The CONVERT () function can display date/time data in different formats.

Grammar

CONVERT (data_type (length),data_to_be_converted,style)

data_type (length) Specifies the target data type (with optional lengths). The data_to_be_converted contains values that need to be converted. style Specifies the date/time output format.

Instance

The following script uses the CONVERT () function to display different formats. We will use the GETDATE () function to get the current date/time:

CONVERT (varchar), GETDATE ()) CONVERT (varchar (), GETDATE (), () CONVERT (varchar (one), GETDATE (), 106) CONVERT ( VARCHAR (in), GETDATE (), 113)

The results are similar:

Dec 11:45 pm12-29-200829 Dec 0829 Dec 2008 16:25:46.635

Referring to the above usage, we modify the code as follows:

Declare @dateFrom datetime;Declare @dateTo   datetime;Declare @str nvarchar( -);Declare @strOne nvarchar( -);Declare @strTwo nvarchar( $);Declare @sql nvarchar( +);Set @dateFrom = '2014-01-01';Set @dateTo = getdate();--Set @strOne = ' and DateCreated >= ' + convert (nvarchar, @dateFrom, +) + ";--Set @strTwo = ' and DateCreated <= ' + convert (nvarchar, @dateTo, +) + ";Set @strOne = 'and t68datecreated between" " + Convert(nvarchar( -),@dateFrom, -)            + " " and" " + Convert(nvarchar( -),@dateTo, -)+ " '";--SELECT * from Authors_t68 where Users between @dateFrom and @dateToSet @str = 'SELECT * from authors_t68 where 1 = 1';--Set @sql = @str + @strOne + @strTwo;Set @sql = @str + @strOne;Print @sql;execsp_executesql@sql

This time, execution, you can query the results. Two splicing methods, commented out using two conditions to limit the time range, the latter method is used Between...and method.