• Common Expression Examples
• Syntax Rules
• Operators
• Conditional Logic
• Numeric Functions
• String Functions
• Date and Time Functions
• NULL Handling
• Type Casting

Common Expression Examples

Syntax Rules

Operators

Arithmetic Operators

Integer division truncates. When both operands are integer fields, the result is also an integer. Use CAST to preserve decimal precision:

CAST(NumeratorField AS NUMERIC) / DenominatorField

Dividing by a field that might be zero — guard with CASE to avoid errors:

CASE WHEN Denominator <> 0 THEN (Numerator / CAST(Denominator AS NUMERIC) * 100) ELSE NULL END

String Concatenation

Use || to join text values:

City || ', ' || State

If any operand may be NULL, the result will also be NULL. Wrap nullable fields in COALESCE:

City || ', ' || COALESCE(State, '')

Comparison and Logical Operators

These are used inside CASE expressions and similar conditionals.

Conditional Logic — CASE

CASE is the primary tool for branching logic in a calculation expression.

Searched CASE evaluates boolean conditions and returns the result for the first matching branch:

CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE defaultResult END

Example with multiple branches:

CASE WHEN Score >= 90 THEN 'A'
     WHEN Score >= 80 THEN 'B'
     WHEN Score >= 70 THEN 'C'
     ELSE 'F'
END

Simple CASE matches one field against specific values:

CASE Status WHEN 'Active' THEN 1 WHEN 'Pending' THEN 2 ELSE 0 END

The LabKey SQL parser sometimes requires additional parentheses within a CASE statement.

Numeric Functions

String Functions

Date and Time Functions

Extracting Date/Time Parts

Date Arithmetic

TIMESTAMPDIFF(interval, timestamp1, timestamp2) finds the difference between two dates at a specified interval:

TIMESTAMPDIFF('SQL_TSI_DAY', CURDATE(), ExpirationDate)
TIMESTAMPDIFF('SQL_TSI_HOUR', StartTime, EndTime)

TIMESTAMPADD(interval, n, timestamp) adds an interval to a date:

TIMESTAMPADD('SQL_TSI_WEEK', 1, CollectionDate)
TIMESTAMPADD('SQL_TSI_MONTH', 6, StorageDate)

Available interval values for both functions:

PostgreSQL note: TIMESTAMPDIFF does not support SQL_TSI_FRAC_SECOND, SQL_TSI_WEEK, SQL_TSI_MONTH, SQL_TSI_QUARTER, or SQL_TSI_YEAR. Use the age functions for month/year differences:

• AGE_IN_MONTHS(date1, date2)
• AGE_IN_YEARS(date1, date2)

NULL Handling

Type Casting

Use CAST to convert a value to a specific data type. This is especially important when dividing integer fields — without casting, LabKey SQL returns a truncated integer result.

CAST(TextField AS INTEGER)
CAST(IntegerField AS NUMERIC)
CAST(DateTimeField AS DATE)
CAST(NumberField AS VARCHAR)
CAST(Value AS NUMERIC(10, 2))

Common cast targets: INTEGER, NUMERIC, DECIMAL, FLOAT, DOUBLE, VARCHAR, DATE, TIMESTAMP, BIGINT, SMALLINT