SQL字符串日期处理.doc

Table 8.2. Commonly Used Text-Manipulation Functions
Function	Description
LEFT() (or use substring function)	Returns characters from left of string
LENGTH() (also DATALENGTH() or LEN())	Returns the length of a string
LOWER()	Converts string to lowercase
LTRIM() (LCASE() if using Access)	Trims white space from left of string
RIGHT() (or use substring function)	Returns characters from right of string
RTRIM()	Trims white space from right of string
SOUNDEX()	Returns a string's SOUNDEX value
UPPER() (UCASE() if using Access)	Converts string to uppercase

 

 

SELECT cust_name, cust_contact

FROM Customers

WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

 

 

日期转换

SELECT order_num

FROM Orders

WHERE DATEPART(yy, order_date) = 2004;

In Access use this version:

SELECT order_num

FROM Orders

WHERE DATEPART('yyyy', order_date) = 2004;

Here is the PostgreSQL version that uses a similar function named DATE_PART():

SELECT order_num

FROM Orders

WHERE DATE_PART('year', order_date) = 2004;

MySQL has all sorts of date manipulation functions, but not DATEPART(). MySQL users can use a function named YEAR() to extract the year from a date:

SELECT order_num

FROM Orders

WHERE YEAR(order_date) = 2004;

Oracle has no DATEPART() function either, but there are several other date manipulation functions that can be used to accomplish the same retrieval. Here is an example:

SELECT order_num

FROM Orders

WHERE to_number(to_char(order_date, 'YY')) = 2004;

SELECT order_num

FROM Orders

WHERE order_date BETWEEN to_date('01-JAN-2004')

AND to_date('31-DEC-2004');

In this example, Oracle's to_date() function is used to convert two strings to dates. One contains the date January 1, 2004, and the other contains the date December 31, 2004. A standard BETWEEN operator is used to find all orders between those two dates. It is worth noting that this same code would not work with SQL Server because it does not support the to_date() function. However, if you replaced to_date() with DATEPART(), you could indeed use this type of statement.

Table 8.3. Commonly Used Numeric Manipulation Functions
Function	Description
ABS()	Returns a number's absolute value
COS()	Returns the trigonometric cosine of a specified angle
EXP()	Returns the exponential value of a specific number
PI()	Returns the value of PI
SIN()	Returns the trigonometric sine of a specified angle
SQRT()	Returns the square root of a specified number
TAN()	Returns the trigonometric tangent of a specified angle

Refer to your DBMS documentation for a list of the supported mathematical manipulation functions