SQL Functions
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 could also use the User Time Token or GETDATE().
Seven Days Ago
This technique can be used 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>
Example
The following field example returns the average donation for Donor.
(SELECT AVG(D.Donation_Amount)
FROM Donations D
WHERE D.Donor_ID = Donors.Donor_ID) AS [First Donation]
Also see 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. Using the asterisk (*) to represent the entire group is common because the database engine is able to 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>
Example
The following field example from a Households View will count the number of Contacts in each Household.
(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. Also see Microsoft Doc: COUNT.
DATEADD
DATEADD alters a date by adding or subtracting units of time. It returns the altered date/time.
Syntax
DATEADD( <date-part>, <number>, <date> )
- Date Part – Determines the type of unit and number to be added.
- Number – The number of units. This can be used 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 may want to just add whole integers to a date. This has the same effect and is more efficient.
Examples
One year ago:
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. Also see 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)
Examples
How many months since a Participant's last attendance:
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. Also see 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 the value can be changed, getting the week or weekday is not always reliable. One way to do it reliably is to use DATENAME.
Examples
The year for the current date:
DATEPART(yy,GETDATE())
The Month for the current date:
DATEPART(mm, GETDATE())
Also see 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)
Example
The day of the week for the current date:
DATENAME(weekday,GETDATE())
For the date 1/1/2000, this would return "Saturday".
Also see Microsoft Doc: DATENAME.
GETDATE
GETDATE returns the current date and time.
Syntax
GETDATE()
Details
GETDATE can be modified 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. Also see Microsoft Doc: GETDATE.
EXISTS
EXISTS checks if a query has any rows and returns True or False.
Syntax
EXISTS ( subquery )
Use
EXISTS is often used in the filter of Views or Dependent Clause of Processes to determine if the record in question has related records with specific values.
Example
In the following example, a View on Contacts with this filter will only return Contacts that are registered in an active Event:
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.
For example, ISNULL can check for a null date and return a date. Here, this technique is combined with GETDATE to account for NULL Event End Dates in a between statement. If the Event_End_Date is NULL, the function uses tomorrow as the date instead:
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. Also see 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>
Example
The following field example returns the earliest donation date for Donor:
(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>
Example
The following field example returns the earliest donation date for Donor:
(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. Also see Microsoft Doc: MIN.
STUFF
The STUFF function inserts a string into another string. It is most valuable for returning 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. Here, the highlighted portion of the example represents a SELECT statement which 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. Also see 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>
Example
The following field example returns the earliest donation date for Donor:
(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.