Basic Date And Text Functions

Why this matters

As a Business Analyst, you will summarize metrics by month or week, calculate delivery times, and clean inconsistent customer text data. Basic date and text functions let you:

• Roll up sales by month/week for KPI tracking.
• Measure lead time (e.g., days from order to shipment).
• Standardize names, emails, and phone numbers for reliable joins and reporting.

Concept explained simply

Date functions help you take a timestamp and get parts of it (year, month, week), align it to a period (start of month/week), or do math (add days, measure differences). Text functions help you combine, split, clean, and compare strings.

Mental model

Think of dates like folders: year > month > day. Truncating picks the folder level you need. Think of text like Lego blocks: you can glue pieces (CONCAT), trim edges (TRIM), swap parts (REPLACE), or take a slice (SUBSTRING).

Key functions you will use

• Extract/Truncate dates: EXTRACT(part FROM ts), DATE_TRUNC('month', ts)
• Date arithmetic: DATEADD(unit, n, ts), ts + INTERVAL 'n unit', DATE_ADD(ts, INTERVAL n UNIT)
• Date difference: DATEDIFF(end, start) or DATE_PART('day', end - start)
• Text case/length: UPPER(), LOWER(), LENGTH()
• Clean/replace: TRIM(), LTRIM(), RTRIM(), REPLACE()
• Concatenate: CONCAT(), CONCAT_WS(' ', a, b)
• Substring: SUBSTRING(str FROM start FOR len), SUBSTR(), LEFT(), RIGHT()

Worked examples

Example 1: Sales by month

Goal: Sum order amounts by month.

-- PostgreSQL style
SELECT DATE_TRUNC('month', order_date) AS order_month,
       SUM(amount) AS total_amount
FROM orders
GROUP BY 1
ORDER BY 1;

-- MySQL style
SELECT DATE_FORMAT(order_date, '%Y-%m-01') AS order_month,
       SUM(amount) AS total_amount
FROM orders
GROUP BY 1
ORDER BY 1;

-- SQL Server style
SELECT DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1) AS order_month,
       SUM(amount) AS total_amount
FROM orders
GROUP BY DATEFROMPARTS(YEAR(order_date), MONTH(order_date), 1)
ORDER BY order_month;

Example 2: Delivery days

Goal: Compute days from order to shipment.

-- PostgreSQL
SELECT id,
       (ship_date::date - order_date::date) AS delivery_days
FROM orders;

-- MySQL
SELECT id,
       DATEDIFF(ship_date, order_date) AS delivery_days
FROM orders;

-- SQL Server
SELECT id,
       DATEDIFF(day, order_date, ship_date) AS delivery_days
FROM orders;

Example 3: Clean customer names and emails

Goal: Title-like display name and email domain extraction.

-- Name cleanup (basic)
SELECT TRIM(LOWER(first_name)) AS first_clean,
       TRIM(LOWER(last_name))  AS last_clean,
       CONCAT(UPPER(LEFT(TRIM(first_name),1)), SUBSTRING(LOWER(TRIM(first_name)),2)) AS first_title,
       CONCAT(UPPER(LEFT(TRIM(last_name),1)),  SUBSTRING(LOWER(TRIM(last_name)),2))  AS last_title
FROM customers;

-- Email parts
-- PostgreSQL
SELECT email,
       LOWER(SPLIT_PART(email, '@', 1)) AS username,
       LOWER(SPLIT_PART(email, '@', 2)) AS domain
FROM customers;

-- MySQL
SELECT email,
       LOWER(SUBSTRING_INDEX(email, '@', 1)) AS username,
       LOWER(SUBSTRING_INDEX(email, '@', -1)) AS domain
FROM customers;

-- SQL Server
SELECT email,
       LOWER(LEFT(email, CHARINDEX('@', email)-1)) AS username,
       LOWER(SUBSTRING(email, CHARINDEX('@', email)+1, LEN(email))) AS domain
FROM customers;

Practice — do it now

These mirror the exercises below (ex1–ex3). You can take the Quick Test anytime; sign in to save your progress, but the test works for everyone.

• ex1: Roll up revenue by month.
• ex2: Extract email username and domain, all lowercased.
• ex3: Calculate delivery days and flag late shipments (> 5 days).

Common mistakes and how to self-check

• Mixing dialects: DATE_TRUNC in MySQL or DATE_ADD in Postgres. Self-check: run a tiny SELECT 1 + 1 to confirm your engine and syntax.
• NULLs in concatenation: 'John' || ' ' || NULL returns NULL in some dialects. Use CONCAT_WS(' ', a, b) or COALESCE parts.
• Negative date differences: wrong argument order. Ensure end date is first (MySQL DATEDIFF(end, start); SQL Server DATEDIFF(day, start, end)).
• Hidden whitespace: emails with spaces fail equality checks. Always TRIM before compare or GROUP BY.
• Month grouping drift: using MONTH(order_date) alone loses the year. Combine YEAR and MONTH or use a proper month start date.

Mini challenge

Create a weekly dashboard query that returns: week_start, total_orders, avg_delivery_days, and the top email domain by order count in that week. Hint: use DATE_TRUNC('week', ...), an aggregate for avg, and a subquery or window function to rank domains.

Who this is for

• Business Analysts new to SQL who need clean reports fast.
• Analysts switching tools and needing portable date/text patterns.

Prerequisites

• Basic SELECT, WHERE, GROUP BY
• Know your SQL dialect (PostgreSQL, MySQL, SQL Server)

Learning path

• Start: Basic Date and Text functions (this page)
• Next: Aggregations with GROUP BY and HAVING
• Then: Joins and data quality checks
• Finally: Window functions for rolling metrics

Practical projects

• Monthly revenue snapshot table with clean customer names and email domains.
• Fulfillment SLA report: delivery_days distribution and late rate by week.
• Customer contact cleanup: normalized phones and deduplicated emails.

Next steps

• Complete the exercises below and take the Quick Test. Anyone can take it; sign in to save progress.
• Apply these functions to your own dataset: start with one KPI and one cleanup task.