Date-time formatted query in SQL statements

Today in the membership management system search, I found that the previous search time method is not too scientific, efficiency is not too high. I have such a time format in the database when the query is equal to the specified time "2007-5-22 14:32:12" When we query for 2007-5-22 whether it is equal to it, the result is not equal.
We all like to find other ways to solve this problem. But I find our methods are not too good, because we sometimes have to SQL sever
The statement feature is not thorough enough to understand. So I looked up SQL Server to help, and finally found a good thing to solve this problem.

The CONVERT function solves this problem by formatting the time of 2007-5-22 14:32:12 in the query or WHERE clause: 2007-5-22;

As follows:
I will query the user's registration time Userregistertime

SELECT * FROM [user] where useractive=0 and replace (CONVERT (char), userregistertime,111), '/', '-') = ' 2007-05-22 ' order by userregistertime Desc

One of the key words in this

Replace (CONVERT (char), userregistertime,111), '/', '-');
Why do I have to replace the "/" in Convert to "-"? It's because convert is here. The formatted time is: 2007/05/22
It is also important to note that the convert format is formatted with a previous 0 format, for example, 2007-1-1 12:12:31 results:
2007-01-01, so be sure to add a leading 0 to the number of months and days in your date that are less than 10 (excluding 10) before you query.
For example, 1 should be 01.

The specific usage of this culvert is as follows:

Conversion functions
When Microsoft®sql server™2000 does not automatically perform expression conversions of different data types, cast and convert can be converted using the transform function. These conversion functions can also be used to obtain a variety of special data formats. Both of these conversion functions can be used to select a list, a WHERE clause, and any place where an expression is allowed to be used.

If you want your Transact-SQL program code to comply with the SQL-92 standard, use CAST instead of CONVERT. If you want to take advantage of the styling feature in convert, use convert instead of CAST.

When using CAST or CONVERT, two messages are required:

The expression to convert (for example, a sales report requires that sales data be converted from currency data to character data).


The data type to which the given expression is to be converted, for example, varchar or any other data type provided by SQL Server.
The conversion is only valid for the duration of the cast or CONVERT function unless the converted value is stored.

In the following example, cast is used in the first SELECT statement, convert is used in the second SELECT statement, and the title column is converted to a char (50) column to make the result more readable:

Use pubs
Select CAST (title as char), ytd_sales
From titles
Where type = ' Trad_cook '

Or

Use pubs
Select CONVERT (char (), title), ytd_sales
From titles
Where type = ' Trad_cook '

Here is the result set: (for any one 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 type int is converted to a char (20) column, so that you can use the like verb for that column:

Use pubs
Select title, ytd_sales
From titles
Where CAST (ytd_sales as char) like ' 15% '
and type = ' Trad_cook '

Here is the result set:

Title ytd_sales

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

Fifty years in Buckingham Palace Kitchens 15096

(1 row (s) affected)

SQL Server automatically handles conversions for some 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 conversion is called implicit transformation. You do not have to use the cast function for these conversions. However, it is acceptable to use CAST in the following situations:

The data type of the two expressions is exactly the same.


Two expressions can be implicitly converted.


The data type must be explicitly converted.
If you attempt an impossible conversion (for example, converting a char expression with a letter to an int type), SQL Server displays an error message.

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

When converting to datetime or smalldatetime, SQL Server rejects any values that are not recognized as dates (including dates prior to January 1, 1753). When the date is within the appropriate range (January 1, 1900 to June 6, 2079), you can convert the datetime value to smalldatetime. The time value is rounded to the nearest number of minutes.

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

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

A char or varchar type expression that is to be converted to a float or real data type can optionally contain an exponential symbol (E or E, followed by an optional + or – symbol, followed by a number).

When you convert a character expression to a data type of a different size, values that are too long for the new data type are truncated, and SQL Server displays an asterisk (*) in the osql utility and SQL Query Analyzer. If the numeric expression is too long for the new data type to be displayed, the value is truncated. The following is an example of character truncation:

Use pubs
Select SUBSTRING (title, 1,) as title, CONVERT (char (2), ytd_sales)
From titles
Where type = ' Trad_cook '

Here is the result set:

Title
------------------------- --
onions, leeks, and Garlic *
Fifty Years in Buckingham *
Sushi, anyone? *

(3 row (s) affected)

When a data type conversion occurs, the value is truncated if the number of decimal digits of the target data type is less than the number of decimal digits of the source data type. For example, the result of CAST (10.3496 as Money) is $10.35.

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

Style parameter
When converting datetime data to char or varchar data, the style parameter of convert provides a variety of date display formats. The values provided for the style parameter determine how the datetime data is displayed. The year can be displayed as either a two-bit or four-digit number. By default, SQL Server displays the year as a two-digit number. To display a four-digit year (yyyy) that includes the century (even if the year data is stored in a two-digit year format), add 100 to the style value to obtain a four-digit year.

How to use convert:

Format:

Convert (Data_type,expression[,style])

Description: This style is typically used when the time type (Datetime,smalldatetime) and the string type (Nchar,nvarchar,char,varchar) are converted to each other.

Example:

Select Convert (varchar (+), GETDATE (), 101) Now

Result is

Now

09/15/2001

The style number has the following meanings when converting time:

Style (2-bit for year)	Style (4-bit for 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 (24 hour system)
14	114	Hh:mi:ss:mmm (24-hour system)
20	120	YYYYMMDD Hh:mi:ss (24-hour system)
21st	121	YYYYMMDD hh:mi:ss:mmm (24-hour system)

The following example shows the conversion with the style parameter:

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

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

Here I enclose some examples:

Select CONVERT (varchar, GETDATE (), +) 2004-09-12 11:06:08 Select Replace (replace (CONVERT (varchar, GETDATE (), , '-', '), ', ', '), ': ', ') 20040912110608 select CONVERT (varchar (), GETDATE (), 111) 2004/09/12 Select CONVERT (varch AR (g), GETDATE (), 20040912 select CONVERT (varchar), GETDATE (), 102) 2004.09.12 select CONVERT (varchar), g Etdate (), 101) 09/12/2004 Select CONVERT (varchar (), GETDATE (), 103) 12/09/2004 select CONVERT (varchar), GETDATE () , 104) 12.09.2004 Select CONVERT (varchar (106), GETDATE (), getdate) 12-09-2004 select CONVERT (varchar, (),) 2004 Select CONVERT (varchar (108), GETDATE (), 107), 2004 select CONVERT (varchar, GETDATE (),) 11:06:0 8 Select CONVERT (varchar (), GETDATE (), 109) 2004 1 Select CONVERT (varchar, GETDATE (), +) 09-12-2004 Sele CT CONVERT (varchar), GETDATE (), 113) 2004 1 Select CONVERT (varchar), GETDATE (), 114) 11:06:08.177

　　

---the date and time in AX 4.0 are stored separately, all time fields are expressed in seconds,

If it is in the Microsoft Dynamics AX front desk, we can easily get the results we want with TIME2STR (createdtime,1,2)

But if you want to display datetime in SQL script, such as the date when the sales order was created, the time

Select top 1 DateAdd (Second, Createdtime, CreatedDate) as createdDateTime from SalesTable
-------------------------------
--eg:2010-06-28 19:30:06.000

　　

Date-time formatted query in SQL statements