SQL date query Problems

Convert the date format during SQL Server date Query
Question:
There are often such query requirements that the date format generated by the input or calendar control is yyyy-mm-dd, and the field in the database is of the datetime type, that is, yyyy-mm-dd hh: mm: SS. If you use the between OR = Statement to query data, the query result may be incorrect or the data is incomplete.
Assume that the database contains data for the period 2008-2-27, but you can use fielddate = '2014-2-27 'or between '2014-2-27' and '2014-2-27 'to query the data for the day.

Solution:
Use the convert function provided by SQL Server for conversion. Since 2008-02-27 is a maximum of 10 bits, use the following statement convert (varchar (10), fielddate, 121) to convert, fielddate is the field name of the data table. In this way, the database automatically converts the information in the database to 10 characters in the yyyy-mm-dd format after obtaining the parameters during the query, and returns the query result if the parameters are the same. in convert, 121 refers to the four-digit year that includes the century digits when converting the datetime type to the char type.
Description of the convert function. The following information is from the network.
Without Century digital (yy) with Century digital (YYYY)
Standard
Input/Output **-0 or 100 (*) Default Value: Mon dd yyyy hh: miam (or PM) 1101 us mm/DD/yyyy2102ansiyy. mm. dd3103 British/French dd/mm/yy4104 German DD. mm. yy5105 Italian dd-mm-yy6106-dd mon yy7107-mon DD, yy8108-hh: mm: ss-9 or 109 (*) Default Value + millisecond mon dd yyyy hh: MI: SS: mmmam (or pm) 10110 us mm-dd-yy11111 Japan YY/MM/dd12112ISOyymmdd-13 or 113 (*) Europe default value + millisecond dd mon yyyy hh: mm: SS: Mmm (24 h) 14114-hh: MI: SS: mmm (24 h)-20 or 120 (*) ODBC Specification Yyyy - Mm-dd hh : Mm : SS [. Fff ]-21 or 121 (*) ODBC specifications (with milliseconds) Yyyy - Mm-dd hh : Mm : SS [. Fff ]-126 (***) ISO8601yyyy-mm-dd thh: mm: SS: Mmm (without spaces)-130 * Kuwait dd mon yyyy hh: MI: SS: mmmAM-131 * Kuwait dd/mm/yy hh: MI: SS: mmmam

* Default value (Style0 or 100, 9 or 109, 13 or 113, 20 or 120, 21 or 121) always returns the digital Century (yyyy ).
** When convertingDatetimeInput; output when converted to character data.
* ** It is specially used for XML. ForDatetimeOrSmalldatetimeToCharacterData conversion. The output format is shown in the table. ForFloat,MoneyOrSmallmoneyToCharacterData conversion and output are equivalentStyle2. ForRealToCharacterData conversion and output are equivalentStyle1.
Use convert:
Convert (data_type [(length)], expression [, style])

Select convert (varchar, getdate (), 120)
11:06:08

Select Replace (replace (convert (varchar, getdate (), 120 ),\'-\',\'\'),\'\', \'\'),\':\',\'\')
20040912110608

Select convert (varchar (12), getdate (), 111)
2004/09/12

Select convert (varchar (12), getdate (), 112)
20040912

Select convert (varchar (12), getdate (), 102)
2004.09.12

Select convert (varchar (12), getdate (), 101)
09/12/2004

Select convert (varchar (12), getdate (), 103)
12/09/2004

Select convert (varchar (12), getdate (), 104)
12.09.2004

Select convert (varchar (12), getdate (), 105)
12-09-2004

Select convert (varchar (12), getdate (), 106)
12 09 2004

Select convert (varchar (12), getdate (), 107)
09 12,200 4

Select convert (varchar (12), getdate (), 108)
11:06:08

Select convert (varchar (12), getdate (), 109)
09 12, 2004 1

Select convert (varchar (12), getdate (), 110)
09-12-2004

Select convert (varchar (12), getdate (), 113)
12 09 2004 1

Select convert (varchar (12), getdate (), 114)
11:06:08. 177