SQL Sever partial constant function and system function usage

(1) Use of mathematical functions1, the absolute value of ABS () function

SELECT ABS (' 2 ')

2. Square root SQRT () function

SELECT SQRT (' 256 ')

3. Squared Square () function

SELECT SQUARE (' 6 ')

(2) string function1. The ASCII value function that returns the first character of a string

ASCII ()

SELECT ASCII (' ABCD ')

2, string conversion large, lowercase functions

LOWER () Convert string to lowercase

UPPER () Convert string to uppercase

SELECT LOWER (' machine ')

SELECT UPPER (' China ')

3. String end-to-end connection using the symbol ' + '

SELECT ' China ' + ' press '

4. The SUBSTRING () function is used to grab a portion of a field data

Format: SUBSTRING (expression,start,length)

Expression: Non-aggregate function

Start: string start position

Length: string lengths

5, reverse () function-Returns the character reversal order of a string (reverse)

SELECT REVERSE (' machine ')

(3) Date, Time function1. GETDATE () function

A very powerful date formatting function in SQL Server
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


Common:
Select CONVERT (varchar), GETDATE (), 8): 10:57:46
Select CONVERT (varchar), GETDATE (), 24): 10:57:47
Select CONVERT (varchar), GETDATE (), 108): 10:57:49
Select CONVERT (varchar), GETDATE (), 12): 060516
Select CONVERT (varchar), GETDATE (), 23): 2006-05-16

2. DateDiff function

Format: DATEDIFF (data-part,date-expression-1,date-expression-2)

Date-part:year,quarter,month,week,day,hour,minute,second,millisecond

SELECT DATEDIFF (year, ' 1996/09/08 ', ' 2017/09/08 ')

(4) System functions

HOST_NAME (): Display host, server name

Suser_name (): Display user login name

USER_NAME (): Displays the name in the user database

Db_name (): Display database name

SQL Sever partial constant function and system function usage