Date and string functions
Dates and labels often arrive in a form that is hard to group or compare. Transform them with a defined purpose and a known SQL dialect.
Text cleaning
TRIM removes surrounding spaces; LOWER and UPPER standardize case. SUBSTR extracts part of a value in SQLite. Cleaning for presentation is different from choosing a stable join key.
Reporting periods
Our dates are stored as ISO YYYY-MM-DD text. SQLite strftime('%Y-%m', order_date) produces a year-month label. PostgreSQL often uses date_trunc; SQL Server has functions such as DATEFROMPARTS and DATETRUNC in supported versions.
Elapsed time
SQLite julianday(end) - julianday(start) calculates a difference in days. SQL Server DATEDIFF counts date-part boundaries, which is not always the same as elapsed duration.
Time zones and meaning
Document the timezone and business-day definition before grouping events into days. A formatted date label is text; use actual date or timestamp values for calculations in databases that support them.