T-SQL function

What are function available in SQL Server

SQL Server supports these analytic functions:

CUME_DIST (Transact-SQL)	LEAD (Transact-SQL)
FIRST_VALUE (Transact-SQL)	PERCENTILE_CONT (Transact-SQL)
LAG (Transact-SQL)	PERCENTILE_DISC (Transact-SQL)
LAST_VALUE (Transact-SQL)	PERCENT_RANK (Transact-SQL)

Analytic functions calculate an aggregate value based on a group of rows. Unlike aggregate functions, however, analytic functions can return multiple rows for each group. Use analytic functions to compute moving averages, running totals, percentages or top-N results within a group.

Aggregate Functions

APPROX_COUNT_DISTINCT	MIN
AVG	STDEV
CHECKSUM_AGG	STDEVP
COUNT	STRING_AGG
COUNT_BIG	SUM
GROUPING	VAR
GROUPING_ID	VARP
MAX

These scalar functions return information about current configuration option settings:

@@DATEFIRST	@@OPTIONS
@@DBTS	@@REMSERVER
@@LANGID	@@SERVERNAME
@@LANGUAGE	@@SERVICENAME
@@LOCK_TIMEOUT	@@SPID
@@MAX_CONNECTIONS	@@TEXTSIZE
@@MAX_PRECISION	@@VERSION
@@NESTLEVEL

These functions support data type casting and conversion:

CAST and CONVERT (Transact-SQL)
PARSE (Transact-SQL)
TRY_CAST (Transact-SQL)
TRY_CONVERT (Transact-SQL)
TRY_PARSE (Transact-SQL)

These functions support digital signing, digital signature validation, encryption, and decryption.

Symmetric encryption and decryption

ENCRYPTBYKEY	DECRYPTBYKEY
ENCRYPTBYPASSPHRASE	DECRYPTBYPASSPHRASE
KEY_ID	KEY_GUID
DECRYPTBYKEYAUTOASYMKEY	KEY_NAME
SYMKEYPROPERTY

Asymmetric encryption and decryption

ENCRYPTBYASYMKEY	DECRYPTBYASYMKEY
ENCRYPTBYCERT	DECRYPTBYCERT
ASYMKEYPROPERTY	ASYMKEY_ID

Signing and signature verification

SIGNBYASYMKEY	VERIFYSIGNEDBYASMKEY
SIGNBYCERT	VERIGYSIGNEDBYCERT
IS_OBJECTSIGNED

Symmetric decryption, with automatic key handling

DecryptByKeyAutoCert

Encryption hashing

HASHBYTES

These scalar functions return information about cursors:

@@CURSOR_ROWS	CURSOR_STATUS
@@FETCH_STATUS

These scalar functions return information about various data type values.

DATALENGTH (Transact-SQL)	IDENT_SEED (Transact-SQL)
IDENT_CURRENT (Transact-SQL)	IDENTITY (Function) (Transact-SQL)
IDENT_INCR (Transact-SQL)	SQL_VARIANT_PROPERTY (Transact-SQL)

The Transact-SQL date and time data types are listed in the following table:

Data type	Format	Range	Accuracy	Storage size (bytes)	User-defined fractional second precision	Time zone offset
time	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 through 23:59:59.9999999	100 nanoseconds	3 to 5	Yes	No
date	YYYY-MM-DD	0001-01-01 through 9999-12-31	1 day	3	No	No
smalldatetime	YYYY-MM-DD hh:mm:ss	1900-01-01 through 2079-06-06	1 minute	4	No	No
datetime	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 through 9999-12-31	0.00333 second	8	No	No
datetime2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999	100 nanoseconds	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)	100 nanoseconds	8 to 10	Yes	Yes

Higher-Precision System Date and Time Functions

Function	Syntax	Return value	Return data type	Determinism
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)	Nondeterministic
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)	Nondeterministic
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	Determinism
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	Nondeterministic
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	Nondeterministic
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	Nondeterministic

Functions That Return Date and Time Parts

Function	Syntax	Return value	Return data type	Determinism
DATENAME	DATENAME ( datepart , date )	Returns a character string representing the specified datepart of the specified date.	nvarchar	Nondeterministic
DATEPART	DATEPART ( datepart , date )	Returns an integer representing the specified datepart of the specified date.	int	Nondeterministic
DAY	DAY ( date )	Returns an integer representing the day part of the specified date.	int	Deterministic
MONTH	MONTH ( date )	Returns an integer representing the month part of a specified date.	int	Deterministic
YEAR	YEAR ( date )	Returns an integer representing the year part of a specified date.	int	Deterministic

Functions That Return Date and Time Values from Their Parts

Function	Syntax	Return value	Return data type	Determinism
DATEFROMPARTS	DATEFROMPARTS ( year, month, day )	Returns a date value for the specified year, month, and day.	date	Deterministic
DATETIME2FROMPARTS	DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision)	Returns a datetime2 value for the specified date and time, with the specified precision.	datetime2( precision )	Deterministic
DATETIMEFROMPARTS	DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds)	Returns a datetime value for the specified date and time.	datetime	Deterministic
DATETIMEOFFSETFROMPARTS	DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)	Returns a datetimeoffset value for the specified date and time, with the specified offsets and precision.	datetimeoffset( precision )	Deterministic
SMALLDATETIMEFROMPARTS	SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )	Returns a smalldatetime value for the specified date and time.	smalldatetime	Deterministic
TIMEFROMPARTS	TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )	Returns a time value for the specified time, with the specified precision.	time( precision )	Deterministic

Functions That Return Date and Time Difference Values

Function	Syntax	Return value	Return data type	Determinism
DATEDIFF	DATEDIFF ( datepart , startdate , enddate )	Returns the number of date or time datepart boundaries, crossed between two specified dates.	int	Deterministic
DATEDIFF_BIG	DATEDIFF_BIG ( datepart , startdate , enddate )	Returns the number of date or time datepart boundaries, crossed between two specified dates.	bigint	Deterministic

Functions That Modify Date and Time Values

Function	Syntax	Return value	Return data type	Determinism
DATEADD	DATEADD (datepart , number , date )	Returns a new datetime value by adding an interval to the specified datepart of the specified date.	The data type of the date argument	Deterministic
EOMONTH	EOMONTH ( start_date [, month_to_add ] )	Returns the last day of the month containing the specified date, with an optional offset.	Return type is the type of the start_date argument, or alternately, the date data type.	Deterministic
SWITCHOFFSET	SWITCHOFFSET (DATETIMEOFFSET , time_zone)	SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value, and preserves the UTC value.	datetimeoffset with the fractional precision of the DATETIMEOFFSET	Deterministic
TODATETIMEOFFSET	TODATETIMEOFFSET (expression , time_zone)	TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. TODATETIMEOFFSET interprets the datetime2 value in local time, for the specified time_zone.	datetimeoffset with the fractional precision of the datetime argument	Deterministic

Functions That Set or Return Session Format Functions

Function	Syntax	Return value	Return data type	Determinism
@@DATEFIRST	@@DATEFIRST	Returns the current value, for the session, of SET DATEFIRST.	tinyint	Nondeterministic
SET DATEFIRST	SET DATEFIRST { number | @number_var }	Sets the first day of the week to a number from 1 through 7.	Not applicable	Not applicable
SET DATEFORMAT	SET DATEFORMAT { format | @format_var }	Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.	Not applicable	Not applicable
@@LANGUAGE	@@LANGUAGE	Returns the name of the language in current used. @@LANGUAGE is not a date or time function. However, the language setting can affect the output of date functions.	Not applicable	Not applicable
SET LANGUAGE	SET LANGUAGE { [ N ] ‘language‘ | @language_var }	Sets the language environment for the session and system messages. SET LANGUAGE is not a date or time function. However, the language setting affects the output of date functions.	Not applicable	Not applicable
sp_helplanguage	sp_helplanguage [ [ @language = ] ‘language‘ ]	Returns information about date formats of all supported languages. sp_helplanguage is not a date or time stored procedure. However, the language setting affects the output of date functions.	Not applicable	Not applicable

Functions That Validate Date and Time Values

Function	Syntax	Return value	Return data type	Determinism
ISDATE	ISDATE ( expression )	Determines whether a datetime or smalldatetime input expression has a valid date or time value.	int	ISDATE is deterministic only used with the CONVERT function, when the CONVERT style parameter is specified, and when style is not equal to 0, 100, 9, or 109.

Use the functions described on the pages in this section to validate or change JSON text or to extract simple or complex values.

ISJSON	Tests whether a string contains valid JSON.
JSON_VALUE	Extracts a scalar value from a JSON string.
JSON_QUERY	Extracts an object or an array from a JSON string.
JSON_MODIFY	Updates the value of a property in a JSON string and returns the updated JSON string.

The following scalar functions perform a calculation, usually based on input values that are provided as arguments, and return a numeric value:

ABS	DEGREES	RAND
ACOS	EXP	ROUND
ASIN	FLOOR	SIGN
ATAN	LOG	SIN
ATN2	LOG10	SQRT
CEILING	PI	SQUARE
COS	POWER	TAN
COT	RADIANS

The following scalar functions return information about the database and database objects:

@@PROCID	INDEX_COL
APP_NAME	INDEXKEY_PROPERTY
APPLOCK_MODE	INDEXPROPERTY
APPLOCK_TEST	NEXT VALUE FOR
ASSEMBLYPROPERTY	OBJECT_DEFINITION
COL_LENGTH	OBJECT_ID
COL_NAME	OBJECT_NAME
COLUMNPROPERTY	OBJECT_SCHEMA_NAME
DATABASE_PRINCIPAL_ID	OBJECTPROPERTY
DATABASEPROPERTYEX	OBJECTPROPERTYEX
DB_ID	ORIGINAL_DB_NAME
DB_NAME	PARSENAME
FILE_ID	SCHEMA_ID
FILE_IDEX	SCHEMA_NAME
FILE_NAME	SCOPE_IDENTITY
FILEGROUP_ID	SERVERPROPERTY
FILEGROUP_NAME	STATS_DATE
FILEGROUPPROPERTY	TYPE_ID
FILEPROPERTY	TYPE_NAME
FULLTEXTCATALOGPROPERTY	TYPEPROPERTY
FULLTEXTSERVICEPROPERTY	VERSION