SQL have a rich set of functions that allow you to get information about strings and modify the contents of those strings in multiple ways.
Functions
LOWER : This function converts alpha character values to lowercase.
Syntax: LOWER(SQL course)
Example:
SELECT LOWER('MANMA') FROM DUAL;
//Output: manma
UPPER : This function converts alpha character values to uppercase.
Syntax: UPPER(SQL course)
Example:
SELECT UPPER('mamna') FROM DUAL;
//Output: MAMNA
CONCAT : This function always appends ( concatenates ) string2 to the end of string1. If either of the string is NULL, CONCAT function returns the non-NULL argument. If both strings are NULL, CONCAT returns NULL.
Syntax:
CONCAT('String1', 'String2')
//Example:
SELECT CONCAT('computer' ,'science') FROM DUAL;
//Output: computerscience
SELECT CONCAT( NULL ,NULL ) FROM DUAL;
//Output: -
CAST and CONVERT
These functions convert an expression of one data type to another.
Example: Change the input datatype
Cast
Example
SELECT 9.5 AS Original,CAST(9.5 AS INT) AS [int], CAST(9.5 AS DECIMAL(6, 4)) AS [decimal];
//Output
Original int decimal
9.5 9 9.50000
Convert
SELECT 9.5 AS Original,CONVERT(INT, 9.5) AS [int],CONVERT(DECIMAL(6, 4), 9.5) AS [decimal];
//Output
Original int decimal
9.5 9 9.50000
TRY_CONVERT
This function tries to convert an expression from one datatype to another datatype. If the conversion fails, the function will return NULL. Otherwise, it will return the converted value.
Syntax
The syntax for the TRY_CONVERT function in SQL Server (Transact-SQL) is:
TRY_CONVERT( type [ (length) ], expression [ , style ] )
type
The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
length
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
expression
The value to convert to another datatype.
style
Optional. The format used to convert between datatypes, such as a date format or string format. It can be one of the following values:
Converting datetime to character
Value (without century) Value (with century) Explanation
0 100 mon dd yyyy hh:miAM/PM (Default)
1 101 mm/dd/yyyy (US standard)
2 102 yy.mm.dd (ANSI standard)
3 103 dd/mm/yy (British/French standard)
4 104 dd.mm.yy (German standard)
5 105 dd-mm-yy (Italian standard)
6 106 dd mon yy
7 107 Mon dd, yy
8 108 hh:mi:ss
9 109 mon dd yyyy hh:mi:ss:mmmAM/PM
10 110 mm-dd-yy (USA standard)
11 111 yy/mm/dd (Japan standard)
12 112 yymmdd (ISO standard)
13 113 dd mon yyyy hh:mi:ss:mmm (Europe standard - 24 hour clock)
14 114 hh:mi:ss:mmm (24 hour clock)
20 120 yyyy-mm-dd hh:mi:ss (ODBC canonical - 24 hour clock)
21 121 yyyy-mm-dd hh:mi:ss:mmm (ODBC canonical - 24 hour clock)
126 yyyy-mm-ddThh:mi:ss:mmm (ISO8601 standard)
127 yyyy-mm-ddThh:mi:ss:mmmZ (ISO8601 standard)
130 dd mon yyyy hh:mi:ss:mmmAM/PM (Hijri standard)
131 dd/mm/yy hh:mi:ss:mmmAM/PM (Hijri standard)
//Example
SELECT TRY_CONVERT(varchar, '2018-09-13', 101);
//Output: '09/13/2018'
TRY_CAST
This function tries to convert an expression from one datatype to another datatype. If the conversion fails, the function will return NULL. Otherwise, it will return the converted value.
Syntax
TRY_CAST( expression AS type [ (length) ] )
expression
The value to convert to another datatype.
type
The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
length
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
Note : When casting from a float or numeric to an integer, the TRY_CAST function will truncate the result. For other conversions, the TRY_CAST function will round the result.
//Example
SELECT TRY_CAST(14.85 AS int);
Result: 14
SELECT TRY_CAST('14 Main St.' AS float);
Result: NULL
(result is NULL because conversion failed since this string value can not be converted to a float)
Date and Time Data Types and Functions
Example:
Functions
LOWER : This function converts alpha character values to lowercase.
Syntax: LOWER(SQL course)
Example:
SELECT LOWER('MANMA') FROM DUAL;
//Output: manma
UPPER : This function converts alpha character values to uppercase.
Syntax: UPPER(SQL course)
Example:
SELECT UPPER('mamna') FROM DUAL;
//Output: MAMNA
CONCAT : This function always appends ( concatenates ) string2 to the end of string1. If either of the string is NULL, CONCAT function returns the non-NULL argument. If both strings are NULL, CONCAT returns NULL.
Syntax:
CONCAT('String1', 'String2')
//Example:
SELECT CONCAT('computer' ,'science') FROM DUAL;
//Output: computerscience
SELECT CONCAT( NULL ,NULL ) FROM DUAL;
//Output: -
CAST and CONVERT
These functions convert an expression of one data type to another.
Example: Change the input datatype
Cast
Example
SELECT 9.5 AS Original,CAST(9.5 AS INT) AS [int], CAST(9.5 AS DECIMAL(6, 4)) AS [decimal];
//Output
Original int decimal
9.5 9 9.50000
Convert
SELECT 9.5 AS Original,CONVERT(INT, 9.5) AS [int],CONVERT(DECIMAL(6, 4), 9.5) AS [decimal];
//Output
Original int decimal
9.5 9 9.50000
TRY_CONVERT
This function tries to convert an expression from one datatype to another datatype. If the conversion fails, the function will return NULL. Otherwise, it will return the converted value.
Syntax
The syntax for the TRY_CONVERT function in SQL Server (Transact-SQL) is:
TRY_CONVERT( type [ (length) ], expression [ , style ] )
type
The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
length
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
expression
The value to convert to another datatype.
style
Optional. The format used to convert between datatypes, such as a date format or string format. It can be one of the following values:
Converting datetime to character
Value (without century) Value (with century) Explanation
0 100 mon dd yyyy hh:miAM/PM (Default)
1 101 mm/dd/yyyy (US standard)
2 102 yy.mm.dd (ANSI standard)
3 103 dd/mm/yy (British/French standard)
4 104 dd.mm.yy (German standard)
5 105 dd-mm-yy (Italian standard)
6 106 dd mon yy
7 107 Mon dd, yy
8 108 hh:mi:ss
9 109 mon dd yyyy hh:mi:ss:mmmAM/PM
10 110 mm-dd-yy (USA standard)
11 111 yy/mm/dd (Japan standard)
12 112 yymmdd (ISO standard)
13 113 dd mon yyyy hh:mi:ss:mmm (Europe standard - 24 hour clock)
14 114 hh:mi:ss:mmm (24 hour clock)
20 120 yyyy-mm-dd hh:mi:ss (ODBC canonical - 24 hour clock)
21 121 yyyy-mm-dd hh:mi:ss:mmm (ODBC canonical - 24 hour clock)
126 yyyy-mm-ddThh:mi:ss:mmm (ISO8601 standard)
127 yyyy-mm-ddThh:mi:ss:mmmZ (ISO8601 standard)
130 dd mon yyyy hh:mi:ss:mmmAM/PM (Hijri standard)
131 dd/mm/yy hh:mi:ss:mmmAM/PM (Hijri standard)
//Example
SELECT TRY_CONVERT(varchar, '2018-09-13', 101);
//Output: '09/13/2018'
TRY_CAST
This function tries to convert an expression from one datatype to another datatype. If the conversion fails, the function will return NULL. Otherwise, it will return the converted value.
Syntax
TRY_CAST( expression AS type [ (length) ] )
expression
The value to convert to another datatype.
type
The datatype that you wish to convert expression to. It can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image.
length
Optional. The length of the resulting data type for char, varchar, nchar, nvarchar, binary and varbinary.
Note : When casting from a float or numeric to an integer, the TRY_CAST function will truncate the result. For other conversions, the TRY_CAST function will round the result.
//Example
SELECT TRY_CAST(14.85 AS int);
Result: 14
SELECT TRY_CAST('14 Main St.' AS float);
Result: NULL
(result is NULL because conversion failed since this string value can not be converted to a float)
Date and Time Data Types and Functions
Data type
|
Format
|
Range
|
Storage size (bytes)
|
User-defined fractional 2nd precision
|
Time zone offset
|
time
|
hh:mm:ss[.nnnnnnn]
|
00:00:00.0000000 through 23:59:59.9999999
|
3 to 5
|
Yes
|
No
|
date
|
YYYY-MM-DD
|
0001-01-01 through 9999-12-31
|
3
|
No
|
No
|
smalldatetime
|
YYYY-MM-DD hh:mm:ss
|
1900-01-01 through 2079-06-06
|
4
|
No
|
No
|
datetime
|
YYYY-MM-DD hh:mm:ss[.nnn]
|
1753-01-01 through 9999-12-31
|
8
|
No
|
No
|
YYYY-MM-DD hh:mm:ss[.nnnnnnn]
|
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999
|
6 to 8
|
Yes
|
No
|
|
datetimeoffset
|
YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm
|
0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in
UTC)
|
8 to 10
|
Yes
|
Yes
|
SELECT CAST('2007-05-08 12:35:29. 1234567 +12:15' AS time(7)) AS 'time',CAST('2007-05-08 12:35:29. 1234567 +12:15' AS date) AS 'date' ,CAST('2007-05-08 12:35:29.123' AS smalldatetime) AS 'smalldatetime',CAST('2007-05-08 12:35:29.123' AS datetime) AS 'datetime' ,CAST('2007-05-08 12:35:29. 1234567 +12:15' AS datetime2(7)) AS 'datetime2',CAST('2007-05-08 12:35:29.1234567 +12:15' AS datetimeoffset(7)) AS 'datetimeoffset';
//Output:
Higher-Precision System Date and Time
Functions
SQL Server 2017 derives the date and time values
through use of the GetSystemTimeAsFileTime() Windows API. The accuracy depends
on the computer hardware and version of Windows on which the instance of SQL
Server running. This API has a precision fixed at 100 nanoseconds. Use the
GetSystemTimeAdjustment() Windows API to determine the accuracy.
Function
|
Syntax
|
Return
value
|
Return
data type
|
SYSDATETIME
|
SYSDATETIME
()
|
Returns
a datetime2(7) value containing the date and time of the
computer on which the instance of SQL Server runs. The returned value does
not include the time zone offset.
|
datetime2(7)
|
SYSDATETIMEOFFSET
|
SYSDATETIMEOFFSET
( )
|
Returns
a datetimeoffset(7) value containing the date and time of
the computer on which the instance of SQL Server runs. The returned value
includes the time zone offset.
|
datetimeoffset(7)
|
SYSUTCDATETIME
|
SYSUTCDATETIME
( )
|
Returns
a datetime2(7) value containing the date and time of the
computer on which the instance of SQL Server is running. The function returns
the date and time values as UTC time (Coordinated Universal Time).
|
datetime2(7)
|
Lower-Precision System Date and Time
Functions
Function
|
Syntax
|
Return
value
|
Return
data type
|
CURRENT_TIMESTAMP
|
CURRENT_TIMESTAMP
|
Returns
a datetime value containing the date and time of the
computer on which the instance of SQL Server runs. The returned value does
not include the time zone offset.
|
datetime
|
GETDATE
|
GETDATE
( )
|
Returns
a datetime value containing the date and time of the computer on which
the instance of SQL Server runs. The returned value does not include the time
zone offset.
|
datetime
|
GETUTCDATE
|
GETUTCDATE
( )
|
Returns
a datetime value containing the date and time of the computer on which
the instance of SQL Server runs. The function returns the date and time
values as UTC time (Coordinated Universal Time).
|
datetime
|
SELECT SYSDATETIME(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), CURRENT_TIMESTAMP, GETDATE(), GETUTCDATE();
/* Returned:
SYSDATETIME() 2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME() 2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP 2007-04-30 13:10:02.047
GETDATE() 2007-04-30 13:10:02.047
GETUTCDATE() 2007-04-30 20:10:02.047
*/
DATENAME
This function returns a character string representing the specified datepart of the specified date.
Syntax
DATENAME ( datepart , date )
//Example
SELECT DATENAME(year, '12:10:30.123'),DATENAME(month, '12:10:30.123'),DATENAME(day, '12:10:30.123'),DATENAME(dayofyear, '12:10:30.123'),DATENAME(weekday, '12:10:30.123');
DATEPART (Transact-SQL)
This function returns an integer representing the specified datepart of the specified date.
Syntax
DATEPART ( datepart , date )
Example:
SELECT DATEPART(week, '2007-04-21 '), DATEPART(weekday, '2007-04-21 ')
//Output :
30 1
datepart
|
datepart
|
datepart
|
datepart
|
datepart
|
year
|
quarter
|
month
|
dayofyear
|
nanosecond
|
day
|
week
|
weekday
|
hour
|
TZoffset
|
minute
|
second
|
millisecond
|
microsecond
|
DAY
This function returns an integer that represents the day (day of the month) of the specified date.
//Syntax
DAY(date)
//Example
SELECT DAY('2015-04-30 01:01:01.1234567');
//Output
30
MONTH
This function Returns an integer that represents the month of the specified date.
//Syntax
MONTH(date)
//Example
SELECT MONTH('2015-04-30 01:01:01.1234567');
//Output
04
YEAR
Returns an integer that represents the year of the specified date.
//Syntax
YEAR(date)
//Example
SELECT YEAR('2015-04-30 01:01:01.1234567');
//Output
2015
Function
|
Syntax
|
DATEFROMPARTS ( year, month, day )
|
|
Example:
SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result;
Result 2010-12-31 |
|
DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision)
|
|
Example:
SELECT DATETIME2FROMPARTS ( 2010, 12, 31, 23, 59, 59, 0,
0 ) AS Result;
Result:
2010-12-31 23:59:59.0000000
|
|
DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds)
|
|
Example:
SELECT DATETIMEFROMPARTS ( 2010, 12, 31, 23, 59, 59, 0 )
AS Result;
Result:
2010-12-31 23:59:59.000
|
|
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)
|
|
Example:
SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23,
23, 0, 12, 0, 7 ) AS Result;
Result :
2010-12-31 14:23:23.0000000 +12:00
|
|
SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )
|
|
Example:
SELECT SMALLDATETIMEFROMPARTS ( 2010, 12, 31, 23, 59 ) AS
Result
Result:
2010-12-31 23:59:00
|
|
TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )
|
|
Example:
SELECT TIMEFROMPARTS ( 23, 59, 59, 0, 0 ) AS Result;
Result:
23:59:59.000000
|
|
DATEDIFF
This function returns the count (as a signed integer value) of the specified datepart boundaries crossed between the specified startdate and enddate
Syntax
DATEDIFF ( datepart , startdate , enddate )
Note : DATEDIFF will not accept datepart values from user-defined variables or as quoted strings.
//Example:
SELECT DATEDIFF(year, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
//Result:
1(for all).
Note: DATEDIFF_BIG
Syntax:
DATEDIFF_BIG ( datepart , startdate , enddate )
Example:
Returns the number of date or time datepart boundaries, crossed between two specified dates.
DATEADD
This function adds a specified number value (as a signed integer) to a specified datepart of an input date value, and then returns that modified value.
Syntax:
DATEADD (datepart , number , date )
//Example
DECLARE @datetime2 datetime2 = '2007-01-01 13:10:10.1111111';
SELECT 'year', DATEADD(year,1,@datetime2)
UNION ALL
SELECT 'quarter',DATEADD(quarter,1,@datetime2)
UNION ALL
SELECT 'month',DATEADD(month,1,@datetime2)
UNION ALL
SELECT 'dayofyear',DATEADD(dayofyear,1,@datetime2)
UNION ALL
SELECT 'day',DATEADD(day,1,@datetime2)
UNION ALL
SELECT 'week',DATEADD(week,1,@datetime2)
UNION ALL
SELECT 'weekday',DATEADD(weekday,1,@datetime2)
UNION ALL
SELECT 'hour',DATEADD(hour,1,@datetime2)
UNION ALL
SELECT 'minute',DATEADD(minute,1,@datetime2)
UNION ALL
SELECT 'second',DATEADD(second,1,@datetime2)
UNION ALL
SELECT 'millisecond',DATEADD(millisecond,1,@datetime2)
UNION ALL
SELECT 'microsecond',DATEADD(microsecond,1,@datetime2)
UNION ALL
SELECT 'nanosecond',DATEADD(nanosecond,1,@datetime2);
//Output
Year 2008-01-01 13:10:10.1111111
quarter 2007-04-01 13:10:10.1111111
month 2007-02-01 13:10:10.1111111
dayofyear 2007-01-02 13:10:10.1111111
day 2007-01-02 13:10:10.1111111
week 2007-01-08 13:10:10.1111111
weekday 2007-01-02 13:10:10.1111111
hour 2007-01-01 14:10:10.1111111
minute 2007-01-01 13:11:10.1111111
second 2007-01-01 13:10:11.1111111
millisecond 2007-01-01 13:10:10.1121111
microsecond 2007-01-01 13:10:10.1111121
nanosecond 2007-01-01 13:10:10.1111111
ISDATE
Returns 1 if the expression is a valid date, time, or datetime value; otherwise, 0.
ISDATE returns 0 if the expression is a datetime2 value.
Syntax
ISDATE(expression)
//Example
IF ISDATE('2009-05-12 10:19:41.177') = 1
PRINT 'VALID'
ELSE
PRINT 'INVALID';
Logical Function
SQL has two logical functions CHOOSE and IIF.
CHOOSE function returns an item from a list of values, based on its position in the list. This position is specified by the index.
In the syntax, the index parameter specifies the item and is a whole number, or integer. The val_1 … val_n parameter identifies the list of values.
SELECT CHOOSE(2, 'Human Resources', 'Sales', 'Admin', 'Marketing' ) AS Result;
//Output:
Sales
IIF function returns one of two values, based on a particular condition. If the condition is true, it will return true value. Otherwise it will return a false value.
In the syntax, the boolean_expression parameter specifies the Boolean expression. The true_value parameter specifies the value that should be returned if the boolean_expression evaluates to true and the false_value parameter specifies the value that should be returned if the boolean_expression evaluates to false.
SELECT BusinessEntityID, SalesYTD, IIF(SalesYTD > 200000, 'Bonus', 'No Bonus') AS 'Bonus?' FROM Sales.SalesPerson
GO
//Example :
BusinessEntityID SalesYTD Bonus?
274 559697.5639 Bonus
275 3763178.1787 Bonus
285 172524.4512 No Bonus
Mathematical Function
For a better understanding, let's see all the preceding functions with easy Example to executes.
Abs(): The Abs() function returns the absolute value. The Abs() function doesn't work for the bit data type.
Syntax
ABS ( numeric_expression )
Example to execute to execute
SELECT ABS(-25) AS A1, ABS(25) AS A2 ,ABS(-25.50) A3 ,ABS(25.25) AS A4
ACOS(): A mathematical function that returns the angle, in radians, whose cosine is the specified float expression, also called arccosine.
Syntax
ACOS ( float_expression )
The return type of the function is float.
Float_expression
Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.
Example to execute
SELECT ACOS(-0.5) A1 ,ACOS(0.5) A2 , ACOS(1) A3
ASIN(): ASIN() function returns the angle, in radians, whose sine is the specified float expression. This is also called arcsine. The return type is Float.
Syntax
ASIN ( float_expression )
float_expression
Is an expression of the type float or of a type that can be implicitly converted to float, with a value from -1 through 1. Values outside this range return NULL and report a domain error.
Example to execute
SELECT ASIN(0.5) A1 , ASIN(0.8) A2 ,ASIN(1) A3
ATAN(): The ATAN() function returns the angle in radians whose tangent is a specified float expression. This is also called arctangent. The return type of the function is float.
Syntax
ATAN ( float_expression )
float_expression: Is an expression of the type float or of a type that can be implicitly converted to float.
Example to execute
SELECT ATAN(45) A1, ATAN(55) A2, ATAN(-25) A3
ATN2(): the ATN2() function returns the angle, in radians, between the positive x-axis and the ray from the origin to the point (y, x), where x and y are the values of the two specified float expressions. The return type of the function is float.
Syntax
ATN2 ( float_expression , float_expression )
Float_expression: Is an expression of the float data type.
Example to execute
SELECT ATN2(45,45) A1 , ATN2(35,148) A2 ,ATN2(88,345) A3
CEILING(): The CEILING() function returns the smallest integer greater than, or equal to, the specified numeric expression. The return type of the function is float.
Syntax
CEILING ( numeric_expression )
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Example to execute
SELECT CEILING(124.45) A1, CEILING(-124.15) A2, CEILING(124) A3
COS(): The COS() function returns the trigonometric cosine of the specified angle, in radians, in the specified expression. In other words, it returns the cosine of an angle that is provided in radians. The return type of the function is float.
Syntax
COS ( float_expression )
Float_expression
Example to execute
SELECT COS(1.046) A1 , COS(0) A2 , COS(0.785) A3
Now we take the same Example to execute for an angle of 60, 0 and 45 degrees.
SELECT COS(60*3.14/180) A1 , COS(0) A2 , COS(45*3.14/180) A3
COT(): The COT() function returns the trigonometric cotangent of the specified angle, in radians, in the specified float expression. In other words it returns the cotangent of an angle in radians. The return type of the function is float.
Syntax
COT ( float_expression )
Float_expression
Is an expression of type float or of a type that can be implicitly converted to float.
Example to execute
SELECT COT(124) A1 ,COT(-45) A2 ,COT(78.45) A3
DEGREES(): The DEGREES() function returns the corresponding angle in degrees for an angle specified in radians. The return type of DEGREES() is the same type as numeric_expression.
Syntax
DEGREES ( numeric_expression )
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Example to execute
SELECT DEGREES(PI()) A1, DEGREES(PI()/2) A2,DEGREES(PI()/4) A3
EXP(): The EXP() function returns the exponential value of the specified float expression. The return type of the function is float. The EXP() function uses the constant e (2.718281…) as the base.
Syntax
EXP ( float_expression )
Example to execute
SELECT EXP(2) A1,EXP(3) A2,EXP(10) A3
FLOOR(): The FLOOR() function returns the largest integer less than or equal to the specified numeric expression. The FLOOR() function returns the same type as numeric_expression.
Syntax
FLOOR ( numeric_expression )
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Example to execute
SELECT FLOOR(-124.14) A1, FLOOR(124) A2 ,FLOOR(124.15) A3
LOG(): The LOG() function returns the natural logarithm of the specified float expression in SQL Server. the return type of the function is float.
Syntax
LOG ( float_expression [, base ] )
By default the value of the base is "e", in other words 22/7.
float_expression : Is an expression of type float or of a type that can be implicitly converted to float.
base: Optional integer argument that sets the base for the logarithm.
Example to execute
SELECT LOG(12) A1, LOG(25) A2, LOG(100) A3
LOG10( ): the LOG10 function returns the base-10 logarithm of the specified float expression. The return type of the function is float.
Syntax
LOG10 ( float_expression )
float_expression
Is an expression of type float or of a type that can be implicitly converted to float.
Example to execute
SELECT LOG10(1000) A1, LOG10(10000) A2, LOG10(10050) A3
PI(): The PI function returns the constant value of PI. The return type is a float that is a constant value.
Syntax
PI ( )
Example to execute
SELECT PI() A1 ,PI()*2 A2 , PI()*3 A3
POWER(): The POWER function returns the value of the specified expression to the specified power. The return type is float.
Syntax
POWER ( float_expression , y )
float_expression: Is an expression of type float or of a type that can be implicitly converted to float.
y: Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Example to execute
SELECT POWER(2,3) A1, POWER(5,5) A2, POWER(4,4) A3
RADIANS(): The RADIANS function returns radians when a numeric expression, in degrees, is entered. The return type of the function is float.
Syntax
RADIANS ( numeric_expression )
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Example to execute
SELECT RADIANS(180) Approximate, RADIANS(180.0) Exact , RADIANS(60) Approximate, RADIANS(60.0) Exact
RAND(): The RAND function returns a pseudo-random float value from 0 through 1, exclusive. The return type of the Rand function is float.
Syntax
RAND ( [ seed ] )
Example to execute
SELECT RAND() A1,RAND() A2, RAND() A3
ROUND(): The ROUND function returns a numeric value, rounded to the specified length or precision. The return type of the function is the same as the numeric expression.
Syntax
ROUND ( numeric_expression , length [ ,function ] )
numeric_expression: Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
length: The number of decimal places rounded to. This value must be a positive or negative integer. If this parameter is omitted, the ROUND function will round the number to 0 decimal places. If it is positive then the decimal part of the number is rounded and if it is negative then the number is rounded on the left side of the decimal point.
function (Optional): The operation can be either 0 or any other numeric value. When it is 0 (or this parameter is omitted), the ROUND function will round the result to the number of decimal_places. If operation is any value other than 0, the ROUND function will truncate the result to the number of decimal_places.
Example to execute
SELECT ROUND(225.715, 2) Round_ UNION all /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, 2, 0) Round_ UNION ALL /* result is rounded because 3rd parameter is 0 */
SELECT ROUND(225.715, 2, 1) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */
SELECT ROUND(225.715, 1) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, 0) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, -1) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(225.715, -2) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(567.655,-2) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(512.566,0) Round_ UNION ALL /* result is rounded because 3rd parameter is omitted */
SELECT ROUND(512.566,-1 ,0) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */
SELECT ROUND(519.566,-1 ,2) Round_ UNION ALL /* result is truncated because 3rd parameter is non-zero */
SELECT ROUND(519.566,-4 ,2) Round_ /* result will be zero because value of Round Place is greater than total length of number */
SIGN(): The SIGN function returns the positive (+1), zero (0), or negative (-1) sign of the specified expression. The return type of the function is the same as the numeric expression.
Syntax:
SIGN ( numeric_expression )
numeric_expression
Is an expression of the exact numeric or approximate numeric data type category, except for the bit data type.
Example to execute
SELECT SIGN(-32.12) A1, SIGN(123.12) A2, SIGN(0.0) A3
SIN( ): The SIN function returns the trigonometric sine of the specified angle, in radians and in an approximate numeric, float, expression. The return type of the function is float.
Syntax
SIN ( float_expression )
float_expression: Is an expression of type float or of a type that can be implicitly converted to float.
Example to execute
SELECT SIN(2.5) A1, SIN(-1.5) A2, SIN(3.0) A3
SQRT(): The SQRT function returns the square root of the specified float value. The return type of the function is float.
Syntax
SQRT ( float_expression )
float_expression: Is an expression of type float or of a type that can be implicitly converted to float.
Example to execute
SELECT SQRT(9) A1,SQRT(25) A2,SQRT(45) A3
SQUARE(): The SQUARE function returns the square of the specified float value. The return type of the function is float.
Syntax
SQUARE ( float_expression )
float_expression
Is an expression of type float or of a type that can be implicitly converted to float.
Example to execute
SELECT SQUARE(4) A1,SQUARE(6.3) A2, SQUARE(-9) A3
TAN(): The TAN function returns the tangent of the input expression. The return type of the function is float.
Syntax
TAN ( float_expression )
float_expression: Is an expression of type float or of a type that can be implicitly converted to float, interpreted as the number of radians.
Example to execute
SELECT TAN(124) A1,TAN(3.12) A2, TAN(-12.12) A3
No comments:
Post a Comment