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_DISTINCTMIN
AVGSTDEV
CHECKSUM_AGGSTDEVP
COUNTSTRING_AGG
COUNT_BIGSUM
GROUPINGVAR
GROUPING_IDVARP
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

ENCRYPTBYKEYDECRYPTBYKEY
ENCRYPTBYPASSPHRASEDECRYPTBYPASSPHRASE
KEY_IDKEY_GUID
DECRYPTBYKEYAUTOASYMKEYKEY_NAME
SYMKEYPROPERTY

Asymmetric encryption and decryption

ENCRYPTBYASYMKEYDECRYPTBYASYMKEY
ENCRYPTBYCERTDECRYPTBYCERT
ASYMKEYPROPERTYASYMKEY_ID

Signing and signature verification

SIGNBYASYMKEYVERIFYSIGNEDBYASMKEY
SIGNBYCERTVERIGYSIGNEDBYCERT
IS_OBJECTSIGNED

Symmetric decryption, with automatic key handling

DecryptByKeyAutoCert

Encryption hashing

HASHBYTES

These scalar functions return information about cursors:

@@CURSOR_ROWSCURSOR_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 typeFormatRangeAccuracyStorage size (bytes)User-defined fractional second precisionTime zone offset
timehh:mm:ss[.nnnnnnn]00:00:00.0000000 through 23:59:59.9999999100 nanoseconds3 to 5YesNo
dateYYYY-MM-DD0001-01-01 through 9999-12-311 day3NoNo
smalldatetimeYYYY-MM-DD hh:mm:ss1900-01-01 through 2079-06-061 minute4NoNo
datetimeYYYY-MM-DD hh:mm:ss[.nnn]1753-01-01 through 9999-12-310.00333 second8NoNo
datetime2YYYY-MM-DD hh:mm:ss[.nnnnnnn]0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999100 nanoseconds6 to 8YesNo
datetimeoffsetYYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 (in UTC)100 nanoseconds8 to 10YesYes

Higher-Precision System Date and Time Functions

FunctionSyntaxReturn valueReturn data typeDeterminism
SYSDATETIMESYSDATETIME ()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
SYSDATETIMEOFFSETSYSDATETIMEOFFSET ( )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
SYSUTCDATETIMESYSUTCDATETIME ( )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

FunctionSyntaxReturn valueReturn data typeDeterminism
CURRENT_TIMESTAMPCURRENT_TIMESTAMPReturns 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.datetimeNondeterministic
GETDATEGETDATE ( )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.datetimeNondeterministic
GETUTCDATEGETUTCDATE ( )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).datetimeNondeterministic

Functions That Return Date and Time Parts

FunctionSyntaxReturn valueReturn data typeDeterminism
DATENAMEDATENAME ( datepart , date )Returns a character string representing the specified datepart of the specified date.nvarcharNondeterministic
DATEPARTDATEPART ( datepart , date )Returns an integer representing the specified datepart of the specified date.intNondeterministic
DAYDAY ( date )Returns an integer representing the day part of the specified date.intDeterministic
MONTHMONTH ( date )Returns an integer representing the month part of a specified date.intDeterministic
YEARYEAR ( date )Returns an integer representing the year part of a specified date.intDeterministic

Functions That Return Date and Time Values from Their Parts

FunctionSyntaxReturn valueReturn data typeDeterminism
DATEFROMPARTSDATEFROMPARTS ( yearmonthday )Returns a date value for the specified year, month, and day.dateDeterministic
DATETIME2FROMPARTSDATETIME2FROMPARTS ( yearmonthdayhourminutesecondsfractionsprecision)Returns a datetime2 value for the specified date and time, with the specified precision.datetime2( precision )Deterministic
DATETIMEFROMPARTSDATETIMEFROMPARTS ( yearmonthdayhourminutesecondsmilliseconds)Returns a datetime value for the specified date and time.datetimeDeterministic
DATETIMEOFFSETFROMPARTSDATETIMEOFFSETFROMPARTS ( yearmonthdayhourminutesecondsfractionshour_offsetminute_offsetprecision)Returns a datetimeoffset value for the specified date and time, with the specified offsets and precision.datetimeoffset( precision )Deterministic
SMALLDATETIMEFROMPARTSSMALLDATETIMEFROMPARTS ( yearmonthdayhourminute )Returns a smalldatetime value for the specified date and time.smalldatetimeDeterministic
TIMEFROMPARTSTIMEFROMPARTS ( hourminutesecondsfractionsprecision )Returns a time value for the specified time, with the specified precision.time( precision )Deterministic

Functions That Return Date and Time Difference Values

FunctionSyntaxReturn valueReturn data typeDeterminism
DATEDIFFDATEDIFF ( datepart , startdate , enddate )Returns the number of date or time datepart boundaries, crossed between two specified dates.intDeterministic
DATEDIFF_BIGDATEDIFF_BIG ( datepart , startdate , enddate )Returns the number of date or time datepart boundaries, crossed between two specified dates.bigintDeterministic

Functions That Modify Date and Time Values

FunctionSyntaxReturn valueReturn data typeDeterminism
DATEADDDATEADD (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 argumentDeterministic
EOMONTHEOMONTH ( 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
SWITCHOFFSETSWITCHOFFSET (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 DATETIMEOFFSETDeterministic
TODATETIMEOFFSETTODATETIMEOFFSET (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 argumentDeterministic

Functions That Set or Return Session Format Functions

FunctionSyntaxReturn valueReturn data typeDeterminism
@@DATEFIRST@@DATEFIRSTReturns the current value, for the session, of SET DATEFIRST.tinyintNondeterministic
SET DATEFIRSTSET DATEFIRST { number | @number_var }Sets the first day of the week to a number from 1 through 7.Not applicableNot applicable
SET DATEFORMATSET DATEFORMAT { format | @format_var }Sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data.Not applicableNot applicable
@@LANGUAGE@@LANGUAGEReturns 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 applicableNot applicable
SET LANGUAGESET 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 applicableNot applicable
sp_helplanguagesp_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 applicableNot applicable

Functions That Validate Date and Time Values

FunctionSyntaxReturn valueReturn data typeDeterminism
ISDATEISDATE ( expression )Determines whether a datetime or smalldatetime input expression has a valid date or time value.intISDATE 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.

ISJSONTests whether a string contains valid JSON.
JSON_VALUEExtracts a scalar value from a JSON string.
JSON_QUERYExtracts an object or an array from a JSON string.
JSON_MODIFYUpdates 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:

ABSDEGREESRAND
ACOSEXPROUND
ASINFLOORSIGN
ATANLOGSIN
ATN2LOG10SQRT
CEILINGPISQUARE
COSPOWERTAN
COTRADIANS

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

@@PROCIDINDEX_COL
APP_NAMEINDEXKEY_PROPERTY
APPLOCK_MODEINDEXPROPERTY
APPLOCK_TESTNEXT VALUE FOR
ASSEMBLYPROPERTYOBJECT_DEFINITION
COL_LENGTHOBJECT_ID
COL_NAMEOBJECT_NAME
COLUMNPROPERTYOBJECT_SCHEMA_NAME
DATABASE_PRINCIPAL_IDOBJECTPROPERTY
DATABASEPROPERTYEXOBJECTPROPERTYEX
DB_IDORIGINAL_DB_NAME
DB_NAMEPARSENAME
FILE_IDSCHEMA_ID
FILE_IDEXSCHEMA_NAME
FILE_NAMESCOPE_IDENTITY
FILEGROUP_IDSERVERPROPERTY
FILEGROUP_NAMESTATS_DATE
FILEGROUPPROPERTYTYPE_ID
FILEPROPERTYTYPE_NAME
FULLTEXTCATALOGPROPERTYTYPEPROPERTY
FULLTEXTSERVICEPROPERTYVERSION

Please follow and like us:

Leave a Comment

Your email address will not be published. Required fields are marked *

error

Enjoy this blog? Please spread the word :)