Stop 03 · SQL for Data AnalysisTopics · 8/10

Date & String Functions

In short

"Sales by month", "orders on weekends", "emails from gmail": real questions need you to slice dates and text. The sad part: each database spells these functions a little differently.

Functions for extracting parts of a date (month, year, day-of-week) and manipulating text (concatenation, substring, trimming) — used constantly in real analyst queries.

The jaadu stamps envelopes with a date stamp and trims a paper strip at a post office counter.

Think of it like this

The post office counter

A post office clerk stamps each letter with the date, reads the PIN code off the address, and trims a torn edge before sorting. Date and string functions are those small tools: pull out the month, read part of the text, tidy it up.

Watch it happen

Slicing one order's text and date

Riya@Gmail.com

2026-03-14 19:42

1/5One messy email and one timestamp. Let's pull useful pieces out of each.

The big ideas

What you need to know

Truncate dates for grouping

DATE_TRUNC('month', order_date) turns every date into the first of its month. Perfect for monthly trends.

Date maths

order_date + INTERVAL '7 days' or CURRENT_DATE - 30 for 'last 30 days'. Syntax varies by database.

Text cleaning

TRIM, LOWER, UPPER, REPLACE and SUBSTRING tidy text before you group or join on it.

Pattern matching

LIKE 'A%' finds text starting with A. ILIKE in PostgreSQL ignores case.

See it in code

Walk through the code

sql

1SELECT2  DATE_TRUNC('month', order_date)        AS month,3  SPLIT_PART(LOWER(TRIM(email)), '@', 2) AS email_domain,4  COUNT(*)                               AS orders5FROM orders6WHERE order_date >= CURRENT_DATE - INTERVAL '90 days'7GROUP BY 1, 28ORDER BY month, orders DESC;

1/4Snap each date to the first of its month so they group.

PostgreSQL syntax: orders per month and email provider for the last 90 days. MySQL uses DATE_FORMAT and SUBSTRING_INDEX instead.

Save yourself the pain

Common mistakes

Oops

Copying a date function from a tutorial for a different database.

Do this instead

Check your database's docs. DATE_TRUNC is PostgreSQL. MySQL, SQL Server and BigQuery each differ.

Oops

Grouping by a raw timestamp and getting one row per second.

Do this instead

Truncate to day, week or month first, then group.

Your mission

Weekend vs weekday

~25 min

Quick check

You want monthly revenue from a table of timestamps. What's the cleanest approach?

Go deeper

1Practice extracting month and year from a date column for a monthly trend query.
2Write a query concatenating first and last name into a full name.
3Learn your specific SQL dialect's date function names (they vary between databases).