SQL statement for date and time formatting query in t-SQL

When searching for the membership management system today, I found that the previous search time method was not scientific, and the efficiency was not too high. the time format "14:32:12" is saved in the database when the specified query time is equal. When we query whether 2007-5-22 is equal to it, the result is not equal.
We all like to find other ways to solve this problem. However, I found that our methods are not very good, because we sometimes use SQL Server
The statement features are not fully understood. So I checked SQL server for help, and finally found a good thing to solve this problem.

The CONVERT function can solve this problem and format the time-5-22 14:32:12 in the query or where clause as:-5-22;

As follows:
I will query the user registration time UserRegisterTime

Select * from [user] where UserActive = 0 and replace (CONVERT (char (10), UserRegisterTime, 111 ),'/','-') = '1970-05-22 'order by UserRegisterTime desc

The key sentence

Replace (CONVERT (char (10), UserRegisterTime, 111 ),'/','-');
Why should I replace the "/" character in convert with "-"? It is because the convert format is: 2007/05/22
Note that the time format of convert is in the format of 0 before it is formatted. For example, the result of 12:12:31 is:
Therefore, before querying, you must add a leading 0 to the number in the month and day of your date, which is less than 10 (not including 10,
For example, 1 should be 01.

The usage of this number is as follows:

Conversion functions
When Microsoft® SQL Server™ 2000 does not automatically CONVERT expressions of different data types, you can use the CAST and CONVERT conversion functions. These conversion functions can also be used to obtain various special data formats. Both conversion functions can be used to select the list, Where clause, and any place Where expressions are allowed.

If you want your Transact-SQL program code to comply with SQL-92 standards, use CAST instead of CONVERT. If you want to use the style function in CONVERT, use CONVERT instead of CAST.

When using CAST or CONVERT, two pieces of information are required:

Expression to be converted (for example, the sales report requires that the sales data be converted from currency data to currency data ).


The data type to be converted to, for example, any other data type provided by varchar or SQL Server.
Unless the converted value is stored, the conversion is only valid during the CAST or CONVERT function's validity period.

In the following example, CAST is used in the first Select statement and CONVERT is used in the second Select statement to CONVERT the title column to the char (50) column to make the result readable:

USE pubs
Select CAST (title AS char (50), ytd_sales
FROM titles
Where type = 'Trad _ cook'

Or

USE pubs
Select CONVERT (char (50), title), ytd_sales
FROM titles
Where type = 'Trad _ cook'

The following is the result set: (for any query)

Ytd_sales
----------------------------------------------------
Onions, Leeks, and Garlic: Cooking Secrets of the 375
Fifty Years in Buckingham Palace Kitchens 15096
Sushi, Anyone? 4095

(3 row (s) affected)

In the following example, the ytd_sales column of the int type is converted to the char (20) column, so that the LIKE predicate can be used for this column:

USE pubs
Select title, ytd_sales
FROM titles
Where CAST (ytd_sales AS char (20) LIKE '20140901'
AND type = 'Trad _ cook'

The following is the result set:

Title ytd_sales

--------------------------------------------------

Fifty Years in Buckingham Palace Kitchens 15096

(1 row (s) affected)

SQL Server automatically converts certain data types. For example, if you compare char and datetime expressions, smallint and int expressions, or char expressions of different lengths, SQL Server can convert them automatically. This type of conversion is called implicit conversion. You do not need to use the CAST function for these conversions. However, it is acceptable to use CAST in the following cases:

The two expressions have the same data type.


Two expressions can be implicitly converted.


The data type must be explicitly converted.
If you try to perform an impossible conversion (for example, convert a char expression containing letters to the int type), SQL Server displays an error message.

If the length of the data type is not specified during conversion, SQL Server automatically provides a length of 30.

When converted to datetime or smalldatetime, SQL Server rejects all values that cannot be recognized as dates (including dates before January 1, January 1, 1753 ). When the date is in the appropriate range (January 1-20, 1900-June 6), The datetime Value can be converted to smalldatetime. The time value is rounded to the nearest minute.

Converting to bit will change any non-zero value to 1.

When converted to money or smallmoney, an integer is added with the currency unit. For example, when an integer 4 is converted to a currency value, it is equivalent to USD 4 (for the default language us_english ). The fractional part of the floating point value is rounded to four decimal places for the money value. The expression of the char or varchar data type to be converted to an integer data type can only contain numbers and optional plus signs or minus signs (+ or -). Leading spaces are ignored. The expression of the char or varchar data type to be converted to money. It can also contain an optional decimal point and a leading dollar sign ($ ).

The char or varchar expression to be converted to float or real data type can also selectively contain exponent symbols (e or E, followed by optional + or-symbols, followed by numbers ).

When you convert a character expression to a data type of another size, the value of the new data type that is too long will be truncated, SQL Server displays an asterisk (*) in the osql utility and SQL query analyzer (*). If the numeric expression is too long to display the new data type, the value is truncated. The following is an example of character truncation:

USE pubs
Select SUBSTRING (title, 1, 25) AS Title, CONVERT (char (2), ytd_sales)
FROM titles
Where type = 'Trad _ cook'

The following is the result set:

Title
---------------------------
Onions, Leeks, and Garlic *
Fifty Years in Buckingham *
Sushi, Anyone? *

(3 row (s) affected)

During data type conversion, if the decimal point of the target data type is smaller than the decimal point of the source data type, the value is truncated. For example, CAST (10.3496 AS money) returns $10.35.

You can explicitly convert text data to char or varchar, and explicitly convert an image to binary or varbinary. Since these data types are limited to 8,000 characters, conversion is also limited to the maximum length of character and binary data types, that is, 8,000 characters. Ntext data can be explicitly converted to nchar or nvarchar, but the maximum length is 4,000 characters. If no length is specified, the default length of the converted value is 30 characters. Implicit conversion is not supported.

Style parameters
When datetime data is converted to char or varchar data, the CONVERT style parameter provides various date display formats. The value provided for the style parameter determines how datetime data is displayed. The year can be displayed as two or four digits. By default, SQL Server displays the year as two digits. To display the four-digit year (yyyy) of the century (even if the year data is stored in two-digit year format), add 100 to the style value to obtain the four-digit year.

How to Use Convert:

Format:

Convert (data_type, expression [, style])

Note: This style is generally used only when the time type (datetime, smalldatetime) and string type (nchar, nvarchar, char, varchar) are converted to each other.

Example:

Select Convert (varchar (30), getdate (), 101) now

Result:

Now

09/15/2001

The conversion time of a style number is as follows:

Style (2 bits indicate the year)	Style (4-digit indicates the year)	Input/output format
0	100	Mon dd yyyy hh: miAM (or PM)
1	101	Mm/dd/yy
2	102	Yymmdd
3	103	Dd/mm/yy
4	104	Ddmmyy
5	105	Ddmmyy
6	106	Dd mon yy
7	107	Mon dd, yy
8	108	Hh: mm: ss
9	109	Mon dd yyyy hh: mi: ss: mmmmAM (or PM)
10	110	Mmddyy
11	111	Yy/mm/dd
12	112	Yymmdd
13	113	Dd mon yyyy hh: mi: ss: mmm (in 24-hour format)
14	114	Hh: mi: ss: mmm (in 24-hour format)
20	120	Yyyymmdd hh: mi: ss (in 24-hour format)
21	121	Yyyymmdd hh: mi: ss: mmm (in 24-hour format)

 

The following example shows how to use the style parameter for conversion:

Select CONVERT (char (12), GETDATE (), 3)

This statement converts the current date to style 3, dd/mm/yy.

Here I have attached some examples:

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

Select 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