SQLMarrow / Course Academy Experience

SQL & Databases Masterclass Course

Welcome to the ultimate postgresql tutorial for beginners 2026. Work through interactive sql practice problems with solutions, master our comprehensive sql window functions tutorial modules, and prepare for tough sql interview questions at Google and Amazon entirely in your browser. Claim your course completion XP and build production-ready database mastery today!

COURSE PROGRESS
0%
0 of 40 Modules Solved
COURSE SYLLABUS
40 Lessons
LESSON WORKSPACEIntermediate

SQL Date Format: How to Work with Dates & Times

Introduction & Core Concept

Time-series tracking is the foundational backbone of financial auditing, user activity cohorts, and sales reporting. SQL provides powerful built-in date formatting functions to parse timelines and compute interval offsets.

In SQLite, we format and extract granular calendar components using strftime (String Format Time) and calculate interval modifications using the date function.

SELECT strftime('%Y-%m', order_date) AS month FROM orders;
💡 MSSQL Tip: Microsoft SQL Server (MSSQL) does not use strftime. Instead, you use FORMAT() or DATEPART()! For example: SELECT FORMAT(order_date, 'yyyy-MM') FROM orders;
Core Date Format Modifiers
  • strftime('%Y', date_col) ➔ Extracts the 4-digit Year.
  • strftime('%m', date_col) ➔ Extracts the 2-digit Month.
  • strftime('%Y-%m', date_col) ➔ Truncates timestamp to Year-Month level.
  • date(date_col, '+30 days') ➔ Calculates future calendar offsets.
    • Why & Where We Use It
  • Why We Use It: Transaction logs contain exact down-to-the-second timestamps. To build monthly revenue dashboards, analysts must truncate timestamps to group rows by month or year.
  • Where We Use It: Monthly recurring revenue (MRR) tracking, identifying subscription renewals, calculating billing cycles, and analyzing daily active users (DAU).
    • Real-World Example

    ApexBank needs an auditing report grouping all retail bank transactions by Year-Month segments to evaluate monthly gross cash flow volumes across the enterprise.

    Best Practices: What to Do & What NOT to Do
  • What to Do: Always store timestamps in UTC (Coordinated Universal Time) format (YYYY-MM-DD HH:MM:SS) in your database schemas to avoid confusing daylight savings time drift across international server regions.
  • What NOT to Do: Never use BETWEEN with raw dates without understanding midnight truncation rules. WHERE date BETWEEN '2026-05-01' AND '2026-05-05' cuts off at exactly 00:00:00 on May 5th, accidentally omitting transactions occurring later that day!
    • Syntax & Pro Tips
    SELECT strftime('%Y', created_at) AS join_year, COUNT(*) 
FROM users GROUP BY join_year;
    Interactive Sandboxed Terminal (Preloaded DB Schema: APEXBANK)
    SQL Query WorkspaceSQLite v3.45 (WASM Mode)
    QUICK INSERT:
    1