SQL Functions
A list of common SQL functions you can use in the Platform.
Date Functions
- GETDATE
- DATEADD
- DATEDIFF
- DATEPART
- DATENAME
- FORMAT (SQL 2012+) — See Microsoft Doc: FORMAT
- MONTH — see Microsoft Doc: MONTH
- DAY — see Microsoft Doc: DAY
- YEAR — see Microsoft Doc: YEAR
- Dynamic Dates
Text Functions
- CONVERT — see Microsoft Doc: CAST and CONVERT
- CAST — see Microsoft Doc: CAST and CONVERT
- CHAR — see Microsoft Doc CHAR
- CHARINDEX — see Microsoft Doc: CHARINDEX
- DATENAME
- FORMAT (SQL 2012+) — See Microsoft Doc: FORMAT
- LEN — see Microsoft Doc: LEN
- REPLACE — see Microsoft Doc: REPLACE
- STUFF
- SUBSTRING — see Microsoft Doc: SUBSTRING
General Functions
- ISNULL
- COALESCE
- CONVERT — See Microsoft Doc: CAST and CONVERT
- CAST — See Microsoft Doc: CAST and CONVERT
- FORMAT (SQL 2012+) — See Microsoft Doc: FORMAT
- TOP — See Microsoft Doc: TOP
Aggregate Functions
- AVG (Average)
- COUNT
- MIN
- MAX
- SUM (Total)
Subquery Functions
- COUNT
- EXISTS
- STUFF
Function Details
Dynamic Dates
Dynamic dates in queries can be much more useful than explicit dates because they are based on the current date. The following methods handle common scenarios. The following examples use the Domain Time Token token, but you could also use the User Time Token or GETDATE().
Seven Days Ago
Use this technique to calculate a date N days ago, such as 7, 30, or 60 days ago.
dp_DomainTime - 7
Previous Calendar Month
MONTH([date_field]) = MONTH(DATEADD(m, -1, dp_DomainTime)) AND YEAR([date_field]) = YEAR(DATEADD(m, -1, dp_DomainTime))
Current Calendar Month
MONTH([date_field]) = MONTH(dp_DomainTime) AND YEAR([date_field]) = YEAR(dp_DomainTime)
Next Calendar Month
MONTH([date_field]) = MONTH(DATEADD(m, 1, dp_DomainTime)) AND YEAR([date_field]) = YEAR(DATEADD(m, 1, dp_DomainTime))
AVG
AVG returns the average value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset.
Basic Syntax
In its most basic form, the AVG returns the grand total for a field value in all records returned by the rest of the query.
AVG( <field-name> ) FROM <from-clause>
(SELECT AVG(D.Donation_Amount)
FROM Donations D
WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]
See also: Microsoft Doc: AVG.
COUNT
COUNT returns the number of items in a group. The group is determined by the FROM clause that follows and may represent all records or distinct records. All is the default.
Basic Syntax
In its most basic form, the COUNT returns the total count of all records returned by the rest of the query. It's common to use the asterisk (*) to represent the entire group because the database engine can easily optimize the query.
COUNT (*) FROM <from-clause>
Distinct Results
Use DISTINCT to return the number of unique non-NULL values returned by rest of the query.
COUNT ( DISTINCT <expression> ) FROM <from-clause>
(SELECT COUNT(C.Contact_ID)
FROM Contacts C
WHERE C.Household_ID = Households.Household_ID) AS [Contact Count]
See View Examples: Groups Over Capacity, Groups Under Capacity, Events Needing Approval, and Group Counts.
See also: Microsoft Doc: COUNT.
DATEADD
DATEADD alters a date by adding or subtracting units of time. It returns the altered date or time.
Syntax
DATEADD( <date-part>, <number>, <date> )
- Date Part – Determines the type of unit and number to be added.
- Number – The number of units. Use this to add or subtract years, months, hours, and so on. To subtract, use a negative value.
- Date – The date to alter.
- Year – yy (or yyyy)
- Month – m (or mm)
- Hour – hh
- Minute – mi (or n)
- Quarter – q (or qq)
- Day of Year – dy (or y)
- Week – wk (or ww)
- Weekday – w (or dw)
- Day – d (or dd) For adding days, you can just add whole integers to a date. This has the same effect and is more efficient.
DATEADD(yy,-1,GETDATE())
This example uses weekday with a DATEDIFF to find the first of the week:
DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)
See View Example: Expiring Product Option Prices.
See also: Microsoft Doc: DATEADD.
DATEDIFF
DATEDIFF returns the difference between two dates.
Syntax
DATEDIFF( <date-part>, <start-date>, <end-date> )
- Date Part – Determines the type of units to calculate between dates (see below).
- Start Date – The first date in the range.
- End Date – The second date in the range.
- Year – yy (or yyyy)
- Month – m (or mm)
- Week – ww (or wk)
- Hour – hh
- Minute – mi (or n)
- Quarter – q (or qq)
- Day of Year – dy (or y)
- Weekday – w (or dw)
- Day – d (or dd)
DATEDIFF(mm,[_Last_Attendance_Date],GETDATE())
This example uses weekday with DATEADD to find the first of the week.
DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)
See View Example: Expiring Product Option Prices.
See also: Microsoft Doc: DATEDIFF.
DATEPART
DATEPART returns part of a date as an integer.
Syntax
DATEPART( <date-part>, <date> )
- Date Part – Determines the type of units to extract from the date (see below).
- Date – The date to retrieve a part from.
- Year – yy (or yyyy)
- Month – m (or mm)
- Day – d (or dd)
- Hour – hh
- Minute – mi (or n)
- Quarter – q (or qq)
- Day of Year – dy (or y)
- Week – wk (or ww)
- Weekday – dw (or weekday)
Week/Weekday
The Week and Weekday uses the first day of the week set in SQL Server. The U.S. English default is Sunday, but it can be changed. To find the current setting you can use:
@@DATEFIRST
Because you can change the value, it's not always reliable to get the week or weekday. One way to do it reliably is to use DATENAME.
DATEPART(yy,GETDATE())
The Month for the current date:
DATEPART(mm, GETDATE())
See also: Microsoft Doc: DATEPART.
DATENAME
DATENAME returns part of a date as a string (text).
Syntax
DATENAME( <date-part>, <date> )
- Date Part – Determines the type of units to extract from the date (see below).
- Date – The date to retrieve a part from.
- Year – yy (or yyyy)
- Month – m (or mm)
- Day – d (or dd)
- Hour – hh
- Minute – mi (or n)
- Quarter – q (or qq)
- Day of Year – dy (or y)
- Week – wk (or ww)
- Weekday – dw (or weekday)
DATENAME(weekday,GETDATE())
For the date 1/1/2000, this returns "Saturday".
See also: Microsoft Doc: DATENAME.
GETDATE
GETDATE returns the current date and time.
Syntax
GETDATE()
Details
Modify GETDATE by adding or subtracting whole days. For example, this is the date and time exactly seven days ago:
GETDATE() - 7
See View Examples: Contact Changes and Group Participants in Selection.
See also: Microsoft Doc: GETDATE.
EXISTS
EXISTS checks if a query has any rows and returns True or False.
Syntax
EXISTS ( subquery )
Use
Use EXISTS in the filter of views or dependent clause of processes to determine if the record in question has related records with specific values.
EXISTS(SELECT 1 FROM Event_Participants EP
JOIN Events E ON E.Event_ID = EP.Event_ID
WHERE EP.Participant_ID = Contacts.Participant_Record
AND GETDATE() BETWEEN E.Event_Start_Date AND ISNULL(E.Event_End_Date,GETDATE()+1))
See View Examples: Contact Changes and Contacts With Milestones.
ISNULL
ISNULL tests a value for NULL and returns the value or a default if the value is NULL.
Syntax
ISNULL( <value>, <replacement-for-null> )
Details
- If the first parameter IS NOT NULL, it will be returned.
- If the first parameter IS NULL, the second parameter will be returned.
GETDATE() BETWEEN Event_Start_Date AND ISNULL([Event_End_Date], GETDATE()+1)
See View Examples: Contact Changes, Group Participants in Selection, and Participant Heads of Household.
See also: Microsoft Doc: ISNULL.
MAX
MAX returns the highest value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset.
Basic Syntax
In its most basic form, the MAX returns the maximum field value in all records returned by the rest of the query.
MAX( <field-name> ) FROM <from-clause>
(SELECT MIN(D.Donation_Date)
FROM Donations D
WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]
See View Example: Group Participant Form Submissions.
See also: Microsoft Doc: MAX.
MIN
MIN returns the lowest value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset. All is the default.
Basic Syntax
In its most basic form, the MIN returns the minimum field value in all records returned by the rest of the query.
MIN( <field-name> ) FROM <from-clause>
(SELECT MIN(D.Donation_Date)
FROM Donations D
WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]
See View Example: New Donors Last Week.
See also: Microsoft Doc: MIN.
STUFF
The STUFF function inserts a string into another string. It is most valuable to return a list of values from a query as a single value when combined with the "FOR XML PATH" clause.
Syntax
STUFF ( <text_expression> , <start> , <length> , <replace_with> )
Use
STUFF is often used on combination with FOR XML PATH to retrieve a comma-delimited list (see below).
STUFF...SELECT...FOR XML PATH — STUFF is often combined with "FOR XML PATH" to return a comma delimited list of values output as a single field. In the syntax below, the SELECT statement returns a field (concatenated with a comma). The "FOR XML PATH" returns the results of the query as a single string. Using STUFF in this way trims the excess comma from the query result.
Syntax
STUFF((SELECT ', ' + <field> FROM <tables> FOR XML PATH('')),1,2,'') AS [Field_Name]
See View Examples: All Event Rooms, Contact Changes, Participant Heads of Household, Contacts With Files Attached, Reports Permitted, Security Role Pages, Tasks Attached to Contacts, and Tools Permitted.
See also: Microsoft Doc: STUFF.
SUM
SUM returns the total value from a group of values. The group is determined by the FROM clause that follows and may represent all records or a subset.
Basic Syntax
In its most basic form, the SUM returns the grand total for a field value in all records returned by the rest of the query.
SUM( <field-name> ) FROM <from-clause>
(SELECT MIN(D.Donation_Date)
FROM Donations D
WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]
See View Examples: Household Donations, Top 50 Donors, and New Donors Last 7 Days.
See also: Microsoft Doc: SUM.