SQL Date & String Functions

By the end of this lesson, you will be able to extract specific components from dates and manipulate text strings using standard SQL functions for cleaner data analysis.

What it is

Date and string functions are built-in tools in SQL that allow you to transform raw data into usable formats. Date functions handle temporal data, enabling you to calculate differences between days or extract years and months. String functions manipulate text, allowing you to change case, trim whitespace, or concatenate values. These operations are essential for preparing data before aggregation or visualization.

Why it matters

• Data Consistency: Standardizes messy input like "John Doe" vs "john doe".
• Time-Based Analysis: Enables grouping sales by month or year instead of individual timestamps.
• Search Optimization: Allows partial matching on names or IDs using pattern recognition.
• Report Readability: Formats numbers and dates for human-friendly output in dashboards.

Syntax or steps

While syntax varies slightly between databases (PostgreSQL, MySQL, SQL Server), most follow a similar pattern: FUNCTION_NAME(argument). For dates, common functions include EXTRACT() or YEAR(). For strings, UPPER(), LOWER(), TRIM(), and CONCAT() are universal.

Example

SELECT 
    -- String manipulation
    UPPER(TRIM(customer_name)) AS clean_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    
    -- Date extraction and formatting
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    
    -- Calculating date difference (days)
    DATEDIFF(delivery_date, order_date) AS days_to_deliver
FROM orders;

This query cleans customer names by removing extra spaces and converting them to uppercase. It then combines first and last names into a single column. Finally, it breaks down the order_date into separate year and month columns and calculates how many days passed between ordering and delivery.

Common mistakes

• Ignoring NULLs: Functions like CONCAT() may return NULL if any argument is NULL. Use COALESCE() to provide default values.
• Case Sensitivity: Remember that 'Apple' and 'apple' are different unless you use LOWER() or UPPER().
• Incorrect Date Formats: Ensure your database recognizes the date format (e.g., YYYY-MM-DD). Invalid formats cause errors or unexpected results.
• Performance Hits: Applying functions to indexed columns in the WHERE clause can prevent index usage. Filter on raw columns when possible.

When to use it

Use these functions during the ETL (Extract, Transform, Load) phase or within analytical queries. Compare with application-level processing:

Practice

Guided Exercise: Write a query that selects all users whose email ends with "@gmail.com" and converts their username to lowercase.

Hint: Use LIKE '%@gmail.com' in the WHERE clause and LOWER(username) in the SELECT.

Challenge: Calculate the age of each employee in years based on their birth_date and today's date.

Solution Hint: Use DATEDIFF(CURDATE(), birth_date) / 365 (MySQL) or AGE(birth_date) (PostgreSQL).

Quick check

Q: What does TRIM(' Hello World ') return?

A: 'Hello World' (it removes leading and trailing whitespace but keeps internal spaces).

Summary

SQL date and string functions are critical for transforming raw data into structured, analyzable formats. Mastering EXTRACT, UPPER, and CONCAT allows you to perform complex cleaning and grouping directly within your database, improving both performance and report accuracy.