T-SQL Functions Reference
Complete reference for all T-SQL function categories with version-specific availability.
Quick Reference
String Functions
| Function |
Description |
Version |
CONCAT(str1, str2, ...) |
NULL-safe concatenation |
2012+ |
CONCAT_WS(sep, str1, ...) |
Concatenate with separator |
2017+ |
STRING_AGG(expr, sep) |
Aggregate strings |
2017+ |
STRING_SPLIT(str, sep) |
Split to rows |
2016+ |
STRING_SPLIT(str, sep, 1) |
With ordinal column |
2022+ |
TRIM([chars FROM] str) |
Remove leading/trailing |
2017+ |
TRANSLATE(str, from, to) |
Character replacement |
2017+ |
FORMAT(value, format) |
.NET format strings |
2012+ |
Date/Time Functions
| Function |
Description |
Version |
DATEADD(part, n, date) |
Add interval |
All |
DATEDIFF(part, start, end) |
Difference (int) |
All |
DATEDIFF_BIG(part, s, e) |
Difference (bigint) |
2016+ |
EOMONTH(date, [offset]) |
Last day of month |
2012+ |
DATETRUNC(part, date) |
Truncate to precision |
2022+ |
DATE_BUCKET(part, n, date) |
Group into buckets |
2022+ |
AT TIME ZONE 'tz' |
Timezone conversion |
2016+ |
Window Functions
| Function |
Description |
Version |
ROW_NUMBER() |
Sequential unique numbers |
2005+ |
RANK() |
Rank with gaps for ties |
2005+ |
DENSE_RANK() |
Rank without gaps |
2005+ |
NTILE(n) |
Distribute into n groups |
2005+ |
LAG(col, n, default) |
Previous row value |
2012+ |
LEAD(col, n, default) |
Next row value |
2012+ |
FIRST_VALUE(col) |
First in window |
2012+ |
LAST_VALUE(col) |
Last in window |
2012+ |
IGNORE NULLS |
Skip NULLs in offset funcs |
2022+ |
SQL Server 2022 New Functions
| Function |
Description |
GREATEST(v1, v2, ...) |
Maximum of values |
LEAST(v1, v2, ...) |
Minimum of values |
DATETRUNC(part, date) |
Truncate date |
GENERATE_SERIES(start, stop, [step]) |
Number sequence |
JSON_OBJECT('key': val) |
Create JSON object |
JSON_ARRAY(v1, v2, ...) |
Create JSON array |
JSON_PATH_EXISTS(json, path) |
Check path exists |
IS [NOT] DISTINCT FROM |
NULL-safe comparison |
Core Patterns
String Manipulation
-- Concatenate with separator (NULL-safe)
SELECT CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName
-- Split string to rows with ordinal
SELECT value, ordinal
FROM STRING_SPLIT('apple,banana,cherry', ',', 1)
-- Aggregate strings with ordering
SELECT DeptID,
STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY HireDate)
FROM Employees
GROUP BY DeptID
Date Operations
-- Truncate to first of month
SELECT DATETRUNC(month, OrderDate) AS MonthStart
-- Group by week buckets
SELECT DATE_BUCKET(week, 1, OrderDate) AS WeekBucket,
COUNT(*) AS OrderCount
FROM Orders
GROUP BY DATE_BUCKET(week, 1, OrderDate)
-- Generate date series
SELECT CAST(value AS date) AS Date
FROM GENERATE_SERIES(
CAST('2024-01-01' AS date),
CAST('2024-12-31' AS date),
1
)
Window Functions
-- Running total with partitioning
SELECT OrderID, CustomerID, Amount,
SUM(Amount) OVER (
PARTITION BY CustomerID
ORDER BY OrderDate
ROWS UNBOUNDED PRECEDING
) AS RunningTotal
FROM Orders
-- Get previous non-NULL value (SQL 2022+)
SELECT Date, Value,
LAST_VALUE(Value) IGNORE NULLS OVER (
ORDER BY Date
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
) AS PreviousNonNull
FROM Measurements
JSON Operations
-- Extract scalar value
SELECT JSON_VALUE(JsonColumn, '$.customer.name') AS CustomerName
-- Parse JSON array to rows
SELECT j.ProductID, j.Quantity
FROM Orders
CROSS APPLY OPENJSON(OrderDetails)
WITH (
ProductID INT '$.productId',
Quantity INT '$.qty'
) AS j
-- Build JSON object (SQL 2022+)
SELECT JSON_OBJECT('id': CustomerID, 'name': CustomerName) AS CustomerJson
FROM Customers
Additional References
For deeper coverage of specific function categories, see:
references/string-functions.md - Complete string function reference with examples
references/window-functions.md - Window and ranking functions with frame specifications
1---
2name: tsql-functions
3description: |
4 Complete T-SQL function reference for SQL Server and Azure SQL Database.
5 PROACTIVELY activate for: (1) string functions (CONCAT_WS, STRING_SPLIT, STRING_AGG, TRIM, REPLACE, LEFT/RIGHT/SUBSTRING), (2) date/time functions (DATEADD, DATEDIFF, FORMAT, DATETRUNC, AT TIME ZONE), (3) math and conversion functions (CAST, CONVERT, TRY_CAST, ROUND, FLOOR, CEILING), (4) window/ranking functions (ROW_NUMBER, RANK, LEAD/LAG, FIRST_VALUE, LAST_VALUE, NTILE), (5) JSON functions (JSON_VALUE, JSON_QUERY, JSON_MODIFY, OPENJSON), (6) XML functions (FOR XML, .nodes, .value), (7) aggregate functions and GROUP BY extensions, (8) system and metadata functions (sys.* views, OBJECT_ID, OBJECT_NAME).
6 Provides: function catalog grouped by category, version-availability matrix (SQL 2016+/2019+/2022+), and worked examples for each function family.
7metadata:
8 source: https://skillmd.com/skills/josiahsiegel/tsql-functions
9---
10
11# T-SQL Functions Reference
12
13Complete reference for all T-SQL function categories with version-specific availability.
14
15## Quick Reference
16
17### String Functions
18| Function | Description | Version |
19|----------|-------------|---------|
20| `CONCAT(str1, str2, ...)` | NULL-safe concatenation | 2012+ |
21| `CONCAT_WS(sep, str1, ...)` | Concatenate with separator | 2017+ |
22| `STRING_AGG(expr, sep)` | Aggregate strings | 2017+ |
23| `STRING_SPLIT(str, sep)` | Split to rows | 2016+ |
24| `STRING_SPLIT(str, sep, 1)` | With ordinal column | 2022+ |
25| `TRIM([chars FROM] str)` | Remove leading/trailing | 2017+ |
26| `TRANSLATE(str, from, to)` | Character replacement | 2017+ |
27| `FORMAT(value, format)` | .NET format strings | 2012+ |
28
29### Date/Time Functions
30| Function | Description | Version |
31|----------|-------------|---------|
32| `DATEADD(part, n, date)` | Add interval | All |
33| `DATEDIFF(part, start, end)` | Difference (int) | All |
34| `DATEDIFF_BIG(part, s, e)` | Difference (bigint) | 2016+ |
35| `EOMONTH(date, [offset])` | Last day of month | 2012+ |
36| `DATETRUNC(part, date)` | Truncate to precision | 2022+ |
37| `DATE_BUCKET(part, n, date)` | Group into buckets | 2022+ |
38| `AT TIME ZONE 'tz'` | Timezone conversion | 2016+ |
39
40### Window Functions
41| Function | Description | Version |
42|----------|-------------|---------|
43| `ROW_NUMBER()` | Sequential unique numbers | 2005+ |
44| `RANK()` | Rank with gaps for ties | 2005+ |
45| `DENSE_RANK()` | Rank without gaps | 2005+ |
46| `NTILE(n)` | Distribute into n groups | 2005+ |
47| `LAG(col, n, default)` | Previous row value | 2012+ |
48| `LEAD(col, n, default)` | Next row value | 2012+ |
49| `FIRST_VALUE(col)` | First in window | 2012+ |
50| `LAST_VALUE(col)` | Last in window | 2012+ |
51| `IGNORE NULLS` | Skip NULLs in offset funcs | 2022+ |
52
53### SQL Server 2022 New Functions
54| Function | Description |
55|----------|-------------|
56| `GREATEST(v1, v2, ...)` | Maximum of values |
57| `LEAST(v1, v2, ...)` | Minimum of values |
58| `DATETRUNC(part, date)` | Truncate date |
59| `GENERATE_SERIES(start, stop, [step])` | Number sequence |
60| `JSON_OBJECT('key': val)` | Create JSON object |
61| `JSON_ARRAY(v1, v2, ...)` | Create JSON array |
62| `JSON_PATH_EXISTS(json, path)` | Check path exists |
63| `IS [NOT] DISTINCT FROM` | NULL-safe comparison |
64
65## Core Patterns
66
67### String Manipulation
68```sql
69-- Concatenate with separator (NULL-safe)
70SELECT CONCAT_WS(', ', FirstName, MiddleName, LastName) AS FullName
71
72-- Split string to rows with ordinal
73SELECT value, ordinal
74FROM STRING_SPLIT('apple,banana,cherry', ',', 1)
75
76-- Aggregate strings with ordering
77SELECT DeptID,
78 STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY HireDate)
79FROM Employees
80GROUP BY DeptID
81```
82
83### Date Operations
84```sql
85-- Truncate to first of month
86SELECT DATETRUNC(month, OrderDate) AS MonthStart
87
88-- Group by week buckets
89SELECT DATE_BUCKET(week, 1, OrderDate) AS WeekBucket,
90 COUNT(*) AS OrderCount
91FROM Orders
92GROUP BY DATE_BUCKET(week, 1, OrderDate)
93
94-- Generate date series
95SELECT CAST(value AS date) AS Date
96FROM GENERATE_SERIES(
97 CAST('2024-01-01' AS date),
98 CAST('2024-12-31' AS date),
99 1
100)
101```
102
103### Window Functions
104```sql
105-- Running total with partitioning
106SELECT OrderID, CustomerID, Amount,
107 SUM(Amount) OVER (
108 PARTITION BY CustomerID
109 ORDER BY OrderDate
110 ROWS UNBOUNDED PRECEDING
111 ) AS RunningTotal
112FROM Orders
113
114-- Get previous non-NULL value (SQL 2022+)
115SELECT Date, Value,
116 LAST_VALUE(Value) IGNORE NULLS OVER (
117 ORDER BY Date
118 ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
119 ) AS PreviousNonNull
120FROM Measurements
121```
122
123### JSON Operations
124```sql
125-- Extract scalar value
126SELECT JSON_VALUE(JsonColumn, '$.customer.name') AS CustomerName
127
128-- Parse JSON array to rows
129SELECT j.ProductID, j.Quantity
130FROM Orders
131CROSS APPLY OPENJSON(OrderDetails)
132WITH (
133 ProductID INT '$.productId',
134 Quantity INT '$.qty'
135) AS j
136
137-- Build JSON object (SQL 2022+)
138SELECT JSON_OBJECT('id': CustomerID, 'name': CustomerName) AS CustomerJson
139FROM Customers
140```
141
142## Additional References
143
144For deeper coverage of specific function categories, see:
145
146- `references/string-functions.md` - Complete string function reference with examples
147- `references/window-functions.md` - Window and ranking functions with frame specifications