(Continued) SQLServer FAQ response
Copyright © ASHUIXU Reprint, please stay intact and indicate
SQL statement part
5, "a headache" date processing
Question: Do you have a problem when you have faced the date?
Answer: Do you understand the following facts (picking from SQLServer2000 online help - DateTime data type: Overview):
A. Date storage.
Datetime
From January 1, 1753 to December 31, 9999, the accuracy is 3% (equal to 3.33 milliseconds or 0.00333 seconds). As shown in the following table, adjust the value to the increment of .000, .003, or .007 seconds.
SmallDateTime
Date and time data from January 1, 2079 from January 1, 1900 to minutes. 29.998 seconds or lower SmallDateTime values are rounded down to the nearest minute, 29.999 seconds or higher SmallDateTime values to the closest minute.
Microsoft SQL Server stores the value of the DateTime data type with two 4-byte integers. The first 4-byte storage base date (i.e., January 1, 1900). The fundamental date is the system reference date. It is not allowed as a datetime value earlier on January 1, 1753. Another 4-byte stored a daily time represented by milliseconds after midnight.
SmallDateTime data type stores the date and daily time, but the accuracy is lower than DateTime. SQL Server stores the value of the SmallDateTime to two 2 bytes of integers. The first 2-byte storage has the number of days after January 1, 1900. Another 2-byte number of minutes after midnight. Date range from January 6, 2079, exactly to minutes from June 6, 2079.
B. There is no independent time and date data type that stores time or date. When setting the DateTime or SmallDateTime value, if only time is specified, the date is default to January 1, 1900. If only the date is specified, the time is default 12:00 AM (midnight).
C. Below is a criterion for some date and time data:
· To accurately search for the date and time, use the equal sign (=). Microsoft SQL Server returns the date and time value of the year, month, and day.
· To search for a date or time, use the LIKE operator. SQL Server first converts the data to the DateTime format and then converts to a VARCHAR format. Since the standard time display format does not include seconds and milliseconds, you cannot use the LIKE and match mode to search, unless the Convert function is used and the Style parameter is 9 or 109. For more information on some dates and time search, see Like.
· SQL Server calculates the DateTime constant at runtime. A date string for a date format for a language expected, if a query is executed by a connection set in different languages and date formats, may not be identified. For example, the following view is set to a US English connection to a US English, but it cannot work properly for other language settings:
Create View USA_DATES AS
SELECT *
From northwind.dbo.orders
WHERE ORDERDATE <'MAY 1, 1997'
When using a DateTime constant in the query, and the query is executed by the connection that is set by different language, you need to ensure that the date can be accepted for all language settings. For DateTime constants in permanent objects in the international database, such as table constraints and view WHERE clauses must be also careful. For more information on all language settings, see Write an international Transact-SQL statement. SQL Server can identify the date and time of the following column format in single quotes ('):
· Alphabetic date format (for example, 'April 15, 1998')
· Digital date format (for example, '4/15/1998', 'April 15, 1998')
· Unlivied string format (for example, '19981207', 'DECEMBER 12, 1998')
6. SQL statement that cannot be determined during design period
Question: When writing the stored procedure, some SQL statements are uncertain during the design period, how is this SQL statement write?
Solution: Use the system stored process sp_executesql.
7, common and important functions, keywords and clauses
A.case - Calculate the list of conditions and return one of multiple possible results expressions
B.isnull - replace NULL using the specified replacement value
C.having clause - Search condition for specifying groups or aggregation. Having is usually used with the Group By clause. If you don't use the Group By clause, Having behavior is the same as where clauses.
