T-SQL database functions

From cast (CONVERT (varchar,d.returncardate,112) + ' +convert (varchar,d.returncartime,108) as DateTime) as Preturncardate Speak up.

Select CONVERT (varchar), GETDATE (), 0): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 1): 05/16/06
Select CONVERT (varchar), GETDATE (), 2): 06.05.16
Select CONVERT (varchar), GETDATE (), 3): 16/05/06
Select CONVERT (varchar), GETDATE (), 4): 16.05.06
Select CONVERT (varchar), GETDATE (), 5): 16-05-06
Select CONVERT (varchar), GETDATE (), 6): 16 05 06
Select CONVERT (varchar), GETDATE (), 7): 05 16, 06
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 9): 2006 10:57:46:827am
Select CONVERT (varchar), GETDATE (), 10): 05-16-06
Select CONVERT (varchar), GETDATE (), 11): 06/05/16
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 13): 16 05 2006 10:57:46:937
Select CONVERT (varchar), GETDATE (), 14): 10:57:46:967
Select CONVERT (varchar), GETDATE (), 20): 2006-05-16 10:57:47
Select CONVERT (varchar), GETDATE (), 21): 2006-05-16 10:57:47.157
Select CONVERT (varchar), GETDATE (): 05/16/06 10:57:47 AM
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 25): 2006-05-16 10:57:47.250
Select CONVERT (varchar), GETDATE (), (+): 2006 10:57am
Select CONVERT (varchar), GETDATE (), 101): 05/16/2006
Select CONVERT (varchar), GETDATE (), 102): 2006.05.16
Select CONVERT (varchar), GETDATE (), 103): 16/05/2006
Select CONVERT (varchar), GETDATE (), 104): 16.05.2006
Select CONVERT (varchar), GETDATE (), 105): 16-05-2006
Select CONVERT (varchar), GETDATE (), 106): 16 05 2006
Select CONVERT (varchar), GETDATE (), 107): 05 16, 2006
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 109): 2006 10:57:49:437am
Select CONVERT (varchar), GETDATE (), 110): 05-16-2006
Select CONVERT (varchar), GETDATE (), 111): 2006/05/16
Select CONVERT (varchar), GETDATE (), 112): 20060516
Select CONVERT (varchar), GETDATE (), 113): 16 05 2006 10:57:49:513
Select CONVERT (varchar), GETDATE (), 114): 10:57:49:547
Select CONVERT (varchar), GETDATE (), 120): 2006-05-16 10:57:49
Select CONVERT (varchar), GETDATE (), 121): 2006-05-16 10:57:49.700
Select CONVERT (varchar), GETDATE (), 126): 2006-05-16t10:57:49.827
Select CONVERT (varchar), GETDATE (), ():???? ?????? 1427 10:57:49:907am
Select CONVERT (varchar), GETDATE (), 131): 18/04/1427 10:57:49:920am

The eyes have been spent, below is the T-SQL database function

1. Aggregation functions

(1) The AVG function returns the average of the values in the group. Null values are ignored by the syntax AVG ([All | DISTINCT] expression) (2) Max function returns the maximum value of the expression syntax max ([all | DISTINCT] expression) (3) Min function function returns the minimum value syntax for the expression min ([all | DISTINCT] expression) (4) The sum function returns all the sum of all values in an expression, or only DISTINCT values. SUM can only be used for numeric columns. The null value is ignored by the syntax SUM ([All | DISTINCT] expression) (5) The Count function function returns the number of items in the group syntax count ({[All | DISTINCT] expression] | * } )

2. Time and date functions

(1) The DATEADD function function returns a new datetime value syntax DATEADD (datepart, number, date) sample SELECT DATEADD (Day, 21, on the basis of adding a period of time to the specified date) pubdate) as timeframe from titles (2) DATEPART function function returns the integer syntax for the specified date portion of the specified date DATEPART (DATEPART, date) sample SELECT DATEPART (month, GETDATE ()) as ' month Number ' (3) the day function returns the integer syntax for the date part that represents the days of the specified date (date) (4) GETDATE function function by datetime value of Microsoft? SQL Server? The standard internal format returns the current system date and time Syntax GETDATE () (5) The getUTCDate function function returns a DateTime value that represents the current UTC time (world time coordinate or Greenwich Mean Time). The current UTC time is from the current local time and the time zone setting syntax in the computer operating system running SQL Server getutcdate () (6) The month function returns the integer syntax that represents the month of the specified date as a month (date) sample Example Select "Month Number" = month (' 03/12/1998 ') (7) the Year function function returns the integer syntax that represents the years in the specified date (date)

3. Mathematical functions

(1) ABS function function returns the absolute value of the given numeric expression. ABS (Numeric_Expression) (2) The Rand function returns the random float value syntax between 0 and 1 rand ([ Seed]): Seed is an integer expression (tinyint, smallint, or int) that gives a seed or start value ( 3) ROUND function function returns a numeric expression and rounds to the specified length or precision syntax ROUND (numeric_e Xpression, length [, Function]) (4) floor function returns the maximum integer syntax floor (numeric_expr) with a return of less than or equal to the maximum number of integers that are less than or equal to the given number expression ession) (5) the Sign function function returns the positive (+1), 0 (0), or negative (-1) Number syntax for the given expression (numeric_expression) (6) the function of the SQUARE function returns to Square syntax for a definite expression square (float_expression) example

3. System functions

(1) CAST and CONVERTThe function function explicitly converts an expression of a data type to another data type. Cast and CONVERT provide similar functional syntax for cast (expression as Data_type) CONVERT (data_type[(length), expression [, style]) sample DECLARE @myval Decimal (5, 2) SET @myval = 193.57 SELECT cast (CAST (@myval as varbinary) as decimal (10,5))-Or, using CONVERT SELECT CONVERT (Decimal (10,5), CONVERT (varbinary (), @myval))(2) CoalesceThe function function returns the first non-empty expression syntax in its argument coalesce (expression [,... n]) sample SELECT CAST (COALESCE (hourly_wage *), salary, commission * num_sales) as money) as "total Salary" from wages(3) datalengthfunction function returns the number of bytes consumed by any expression syntax datalength (expression)(4) @ @ERRORThe function function returns the error code syntax for the last executed Transact-SQL statement @ @ERROR Sample IF @ @ERROR = 547 print "A check constraint violation occurred"(5) ISDATEfunction function Determines whether the input expression is a valid date syntax ISDATE (expression)(6) ISNULLThe function function replaces the NULL syntax with the specified substitution value ISNULL (check_expression, Replacement_value)(7) IsNumericfunction function Determines whether an expression is a valid numeric type syntax isnumeric (expression)(8) Nulliffunction function returns null syntax nullif (expression, expression) if two specified expressions are equal(9) @ @ROWCOUNTfunction function returns the number of rows affected by the previous statement syntax @ @ROWCOUNT

4. Cursor functions

(1) @ @CURSOR_ROWS function returns the number of qualifying rows that currently exist in the last cursor opened on the connection. To improve performance, Microsoft? SQL Server? Large keyset and static cursors can be populated asynchronously. The @ @CURSOR_ROWS can be called to determine when it is called, the number of rows that match the cursor is retrieved syntax @ @CURSOR_ROWS sample SELECT @ @CURSOR_ROWS DECLARE authors_cursor cursor for SE Lect au_lname from authors OPEN authors_cursor FETCH NEXT from authors_cursor SELECT @ @CURSOR_ROWS CLOSE authors_cursor DE ALLOCATE authors_cursor (2) @ @FETCH_STATUS function function returns the state of the last cursor executed by the FETCH statement, rather than the state syntax of any cursor currently being connected open @ @FETCH_STATUS sample D Eclare employee_cursor Cursor for SELECT LastName, FirstName from Northwind.dbo.Employees OPEN employee_cursor FETCH NEXT From Employee_cursor when @ @FETCH_STATUS = 0 BEGIN FETCH NEXT from Employee_cursor END CLOSE employee_cursor deallocat E Employee_cursor

5. Meta-Data functions

(1) col_length function returns the definition length of a column (in bytes) syntax col_length (' table ', ' column ') (2) Col_name function returns the name of the database column With the corresponding table identification number and column identification number Syntax Col_name (table_id, column_id) (3) Index_col function function returns the index column name Syntax index_col (' table ', index_id , key_id) (3) Db_name function function returns the database name syntax db_name (database_id) (3) object_id function function returns the database object ID syntax object_id (' object ') sample SELECT object_id (' pubs.. Authors ') (4) object_name function function returns the database object name syntax object_name (object_id) (5) @ @PROCID function function returns the stored procedure identifier for the current procedure (I D) syntax @ @PROCID (6) Typeproperty function function returns information about the data type syntax Typeproperty (type, property) sample SELECT Typeproperty (' Tin Yint ', ' PRECISION ')

5. String functions

(1) ASCIIfunction function returns the ASCII code value of the leftmost character of the character expression syntax ASCII (character_expression)(2) CHARfunction function Convert int ASCII code to character string function syntax CHAR (integer_expression)(3) CHARINDEXfunction function returns the starting position of the specified expression in the string CHARINDEX (expression1, expression2 [, start_location]) parameter expression1: An expression that contains the order of the characters to look for. Expression1 is an expression of a short character data type classification. Expression2: An expression, typically a column that is used to search for a specified sequence. Expression2 belongs to the string data type classification.(4) Differencefunction function returns the difference between the SOUNDEX value of a two-character expression in integers syntax difference (character_expression, character_expression)(5) leftfunction function returns the character syntax for the specified number of characters from the left of the string (character_expression, integer_expression)(6) LENfunction function returns the number of characters (not bytes) of the given string expression, with no trailing space syntax LEN (string_expression)(7) LOWERfunction function returns character expression syntax LOWER (character_expression) after converting uppercase character data to lowercase characters(8) LTRIMfunction function removes the starting space after the return character expression syntax LTRIM (character_expression)(9) NCHARfunction function returns the Unicode character syntax NCHAR (integer_expression) sample with the given integer code, as defined by the Unicode standard(Ten) PATINDEXfunction function returns the starting position of the first occurrence of a pattern in a specified expression, and returns 0 syntax PATINDEX ('%pattern% ', expression) if the pattern is not found in all valid text and character data types(one) peplaceThe function function replaces all occurrences of the second given string expression in the first string expression with a third expression, replace (' String_expression1 ', ' string_expression2 ', ' String_expression3 ‘ )(REVERSE)function function returns the inverse syntax of a character expression REVERSE (character_expression)(+) rightfunction function returns the integer_expression character syntax for the specified number of characters in the string right (character_expression, integer_expression)(RTRIM)function function truncates all trailing spaces after returning a string syntax RTRIM (character_expression)(SOUNDEX)function function returns a four-character code (SOUNDEX) to evaluate the similarity syntax of two strings SOUNDEX (character_expression)

Finally, it is time to learn the downstream standard, the previous study of the full remember

T-SQL database functions