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

Source: Internet
Author: User

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.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.