Please enable JavaScript.
Coggle requires JavaScript to display documents.
Skill 1.3 - Functions & Aggregate Data - Coggle Diagram
Skill 1.3 - Functions & Aggregate Data
Type Conversions
CAST
:recycle:TRY_CAST
CONVERT
:recycle:TRY_CONVERT
PARSE
FORMAT
.NET e.g. FORMAT(SYSDEATETIME(),'yyyy-MM-dd')
Data & Time Functions
Current Date and Time
GETDATE
CURRENT_TIMESTAMP
Standard - so recommended
GETUTCDATE
SYSDATETIME
DATETIME2
SYSUTCDATETIME
SYSDATETIMEOFFSET
DATETIME2
Date and Time Parts
DATEFROMPARTS
DATEFROMPARTS
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DATETIMEOFFSETFROMPARTS
SMALLDATETIMEFROMPARTS
TIMEFROMPARTS
DATEPART
YEAR
MONTH
DAY
DATENAME
EOMONTH
Add and Diff Functions
DATEADD
DATEDIFF
DATEDIFF_BIG
Offset
Systems Functions
E.g.
@@RowCount
INT - Max 2,147,483,647 alternative ROWCOUNT_BIG BIGINT
GUID & ID Functions
NEWID()
Globally Unique
UNIQUEIDENTIFIER Type
@@IDENTITY
System Function
Last Value regardless of Scope
SCOPE_IDENTITIY
Last ID generated in session
Character Functions
Concatenation
A + B = AB
:warning:Causes NULLS if one items is NULL
COALESCE or ISNULL
CONCAT
:warning:Ignores NULLS
Substring Extraction and Position
String Length
LEN
Ignores Whitespace
DATALENGTH
BYTEs (2 per character)
Includes Whitespace
Formating
UPPER
LOWER
LTRIM
RTRIM
FORMAT
Spitting Table-Valued Function
SPRING_SPLIT
Arithmetic Operators & Aggregate Functions
+, *, /, -, %
Precisioon
SUM, COUNT, MIN, MAX, AVG
Function Determinism
A Deterministic function is guaranteed to return the same output across calls given the same inputs
CASE & related Functions
Simple Form
CASE [COLUMN] WHEN 0 THEN 'No' WHEN 1 THEN 'Yes' ELSE 'Unknown'
Searched Form
Predicates are excluded in order
Related Functions
COALESCE
1st Value not a null COALESCE(exp1, exp2,... expN) can be used to substitute Null COALESCE([Column],'') but now the same as ISNULL([Column],'')
Datatype determined by whichever item is returned.
NULLIF
ISNULL
Datatype determined by the 1st item regardless of whether it is returned. :warning:This affects the SELECT INTO Statement if there isa either a ISNULL or COALESCE function used.
IIF
Non Standard
IFF(predicate, true, false(or unknown) = CASE WHEN predicate THEN 'True' ELSE 'False' End
CHOOSE
Non Standard
CHOOSE(pos, exp1, exp2,... expN)
Search Arguments
=,>,>=,<=, Between, Like, <>, !=