KysoqlDate functions and timezones
Filter, group, and order temporal expressions with typed function identities.
Date literal helpers create values such as 2026-09-01. Date functions create expressions over Salesforce fields, such as CALENDAR_YEAR(CloseDate).
Supported functions
| TypeScript | SOQL | Input | Result |
|---|---|---|---|
calendarMonth | CALENDAR_MONTH | Date or DateTime | Number |
calendarQuarter | CALENDAR_QUARTER | Date or DateTime | Number |
calendarYear | CALENDAR_YEAR | Date or DateTime | Number |
dayInMonth | DAY_IN_MONTH | Date or DateTime | Number |
dayInWeek | DAY_IN_WEEK | Date or DateTime | Number |
dayInYear | DAY_IN_YEAR | Date or DateTime | Number |
fiscalMonth | FISCAL_MONTH | Date or DateTime | Number |
fiscalQuarter | FISCAL_QUARTER | Date or DateTime | Number |
fiscalYear | FISCAL_YEAR | Date or DateTime | Number |
weekInMonth | WEEK_IN_MONTH | Date or DateTime | Number |
weekInYear | WEEK_IN_YEAR | Date or DateTime | Number |
hourInDay | HOUR_IN_DAY | DateTime | Number |
dayOnly | DAY_ONLY | DateTime | Date string |
convertTimezone | convertTimezone | DateTime | DateTime expression |
Nullability follows the source field and relationship. Fiscal results still depend on the org's fiscal calendar configuration.
Filter with a date function
You do not need GROUP BY to use date functions in WHERE:
import { soqlDate } from "@kysoql/core";
import { db } from "./db";
const query = db
.selectFrom("Opportunity")
.select(["Id", "Name"])
.where((eb) =>
eb.and([
eb(eb.fn.calendarYear("CloseDate"), "=", 2026),
eb(
eb.fn.dayOnly(eb.fn.convertTimezone("CreatedDate")),
">=",
soqlDate("2026-09-01"),
),
]),
);Numeric date functions compare with numbers. dayOnly() returns a date expression, so compare it with soqlDate().
Group by the same expression you select
import { db } from "./db";
const byYear = db
.selectFrom("Opportunity")
.select(({ fn }) => fn.sum("Amount").as("totalAmount"))
.groupBy(({ fn }) => fn.calendarYear("CloseDate"))
.select(({ fn }) => fn.calendarYear("CloseDate").as("closeYear"))
.having((eb) => eb(eb.fn.calendarYear("CloseDate"), ">=", 2025))
.orderBy(({ fn }) => fn.calendarYear("CloseDate"));Grouped expressions are matched by their full identity. calendarYear("CloseDate") is different from calendarMonth("CloseDate") and from the same function applied to another field.
Use the Salesforce user's timezone
import { db } from "./db";
const activity = db
.selectFrom("Opportunity")
.select(({ fn }) => fn.count("Id").as("opportunityCount"))
.groupBy(({ fn }) => fn.hourInDay(fn.convertTimezone("CreatedDate")))
.select(({ fn }) =>
fn.hourInDay(fn.convertTimezone("CreatedDate")).as("localHour"),
)
.orderBy(({ fn }) => fn.hourInDay(fn.convertTimezone("CreatedDate")));convertTimezone() uses the querying Salesforce user's timezone. It does not use the timezone of your Node.js process.
A converted expression is also a different grouping expression from the unconverted field.
Exact Date grouping has one useful exception
If you group by a raw Date field, you can also select a date function over that same field:
import { db } from "./db";
const byExactDate = db
.selectFrom("Opportunity")
.select(({ fn }) => fn.count("Id").as("opportunityCount"))
.groupBy("CloseDate")
.select(({ fn }) => fn.calendarYear("CloseDate").as("closeYear"));The rows are still grouped by exact CloseDate, not by year. This exception does not extend to DateTime grouping, HAVING, date-function ordering, ROLLUP, or CUBE.