(continued) SQL Server FAQ Response

server|sqlserver| problem
(continued) SQL Server FAQ Response































All rights reserved ©ashuixu reprint please remain complete and indicate the source































SQL statement Section































5, "a headache" date processing

Question: Do you have any hesitation in dealing with the date?

Answer: Do you know the following facts (pick from SQLServer2000 online help-datetime data type: Overview):

















A. Storage of dates.



Datetime

Date and time data from January 1, 1753 to December 31, 9999, with an accuracy of 3% seconds (equal to 3.33 milliseconds or 0.00333 seconds). Adjust the value to the increment of. 000,. 003, or. 007 seconds, as shown in the following table.



smalldatetime

Date and time data from January 1, 1900 to June 6, 2079 are accurate to minutes. The smalldatetime value of 29.998 seconds or less is rounded down to the nearest minute, and the smalldatetime value of 29.999 seconds or higher is rounded up to the nearest minute.































Microsoft SQL Server Stores the value of a datetime data type within two 4-byte integers. The first 4 bytes stores the number of days before or after base date (i.e. January 1, 1900). The base date is the system reference date. DateTime values older than January 1, 1753 are not allowed. Another 4-byte store is the daily time represented by the number of milliseconds after midnight.































The smalldatetime data type stores the date and the time of day, but the accuracy is less than DateTime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. Another 2 byte stores the number of minutes after midnight. Date range from January 1, 1900 to June 6, 2079, accurate to minutes.































B. There are no independent time and date data types that store only the time or date. When you set a datetime or smalldatetime value, if you specify only the time, the date defaults to January 1, 1900. If you specify only a date, the time defaults to 12:00 AM (midnight).

















C. Here are some guidelines for using date and time data:















· To make an exact search match for a date and time, use an equal sign (=). Microsoft SQL Server Returns a date and time value that matches the year, month, and day exactly in the exact time of 12:00:00:000 AM (the default).



· To search for a part of a date or time, use the LIKE operator. SQL Server first converts data to datetime format before converting to varchar format. Because the standard time display format does not include seconds and milliseconds, you cannot use like and match modes to search for them unless you use the CONVERT function and set the style argument to 9 or 109. For more information about partial date and time searches, see like.































· SQL Server computes a DateTime constant at run time. A date string for the date format expected in a language, which may not be recognized if the query is executed by a connection that is set up with different language and date formats. For example, the following view works correctly for a connection that has a language set to U.S. English, but a connection that is set for other languages does not work:















CREATE VIEW Usa_dates as

SELECT *

From Northwind.dbo.Orders

WHERE OrderDate < ' May 1, 1997 '















When you use a DateTime constant in a query, and the query is executed by a connection that uses different language settings, make sure that the date is acceptable for all language settings. You must be equally cautious about DateTime constants in persistent objects in international databases, such as table constraints and viewing WHERE clauses. For more information about all language settings that are interpreted as the same date format, see Writing International Transact-SQL statements.















SQL Server can identify the date and time enclosed in single quotes (') in the following format:















· Letter date format (for example, ' April 15, 1998 ')















· Digital date format (for example, ' 4/15/1998 ', ' April 15,1998 ')















· An unbound string format (for example, ' 19981207 ', ' December 12, 1998 ')















6, the design period can not determine the SQL statement















Question: When writing a stored procedure, some SQL statements are indeterminate during the design period, so how do you write SQL statements?















Answer: Use system stored procedure sp_executesql.































7. Common and important functions, keywords and clauses















A.case-Calculates a list of conditions and returns one of several possible result expressions















B.isnull-Replaces NULL with the specified replacement value















c.having clause-Specifies the search criteria for a group or aggregation. Having is typically used with the GROUP by clause. If you do not use the GROUP by clause, the having behavior is the same as WHERE clause.