SQL Date Format: Complete Guide with Interactive Examples

Formatting dates is one of the most common tasks in SQL. Whether you’re building reports, creating user-friendly displays, or grouping data by time periods, you need to know how to convert dates into the right format.

Throughout this guide, you’ll find interactive SQL exercises that let you practice date formatting directly in your browser. Write queries, get instant feedback, and master SQL date functions without any database setup required.

Understanding SQL Date Formatting

SQL databases store dates in a standardized internal format, but when you display them to users or use them in reports, you often need a different format. That’s where date formatting functions come in.

The most versatile date formatting function is strftime (string format time), which is available in SQLite and has equivalents in other databases:

The interactive examples in this tutorial use SQLite’s strftime() function, which runs directly in your browser.

Basic Date Formatting with strftime

The strftime() function takes a format string and a date value, returning a formatted string. Here’s the basic syntax:

strftime(format, date_value)

The format string uses special codes starting with % to represent different parts of the date:

SELECT
  order_date,
  strftime('%Y-%m-%d', order_date) as iso_date,
  strftime('%d/%m/%Y', order_date) as european_date,
  strftime('%m-%d-%Y', order_date) as us_date
FROM orders;

This query shows the same date in three common formats:

• ISO format: 2026-01-15 (international standard)
• European format: 15/01/2026 (day first)
• US format: 01-15-2026 (month first)

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Common Date Format Codes

Here’s a quick reference for the most useful format codes:

You can combine these codes with any text to create custom formats:

SELECT strftime('%B %d, %Y', '2026-03-15') as formatted;
-- Result: March 15, 2026

Formatting Dates for Reports

One of the most common use cases for date formatting is creating monthly or quarterly reports. You need to group data by time periods and display those periods in a readable way.

SELECT
  strftime('%Y-%m', sale_date) as month,
  COUNT(*) as total_sales,
  SUM(amount) as revenue
FROM sales
GROUP BY strftime('%Y-%m', sale_date)
ORDER BY month;

This query groups sales by month (YYYY-MM format) and calculates totals for each period. The format makes it easy to sort chronologically while still being human-readable.

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Extracting Date Parts

Sometimes you need to extract specific parts of a date rather than format the entire thing. The strftime() function works for this too:

SELECT
  order_date,
  strftime('%Y', order_date) as year,
  strftime('%m', order_date) as month,
  strftime('%d', order_date) as day,
  strftime('%H', order_date) as hour
FROM orders;

This is useful for:

• Filtering by date parts: Find all orders from March
• Grouping: Group by year, quarter, or day of week
• Calculations: Compare values across the same month in different years

For databases with a dedicated EXTRACT() function, you can also use:

-- PostgreSQL and others
SELECT EXTRACT(MONTH FROM order_date) as month FROM orders;

Creating Human-Readable Dates

When displaying dates to end users, you want something more friendly than “2026-03-15”. You can combine format codes to create readable output:

SELECT
  customer_name,
  strftime('%a, %b %d at %H:%M', appointment_time) as display_time
FROM appointments;
-- Example output: "Mon, Mar 15 at 14:30"

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Working with Day of Week

The day of week is essential for scheduling, shift planning, and business logic. In SQLite, %w returns a number from 0 (Sunday) to 6 (Saturday):

SELECT
  event_name,
  strftime('%w', event_date) as day_number,
  CASE strftime('%w', event_date)
    WHEN '0' THEN 'Sunday'
    WHEN '1' THEN 'Monday'
    WHEN '2' THEN 'Tuesday'
    WHEN '3' THEN 'Wednesday'
    WHEN '4' THEN 'Thursday'
    WHEN '5' THEN 'Friday'
    WHEN '6' THEN 'Saturday'
  END as day_name
FROM events;

This pattern is useful for:

• Filtering weekdays only: WHERE strftime('%w', date_col) BETWEEN '1' AND '5'
• Weekend analysis: WHERE strftime('%w', date_col) IN ('0', '6')
• Day-specific reports: Group sales by day of week to find busiest days

Try it yourself:

[[ error ]]

[[ testData.solution ]]

Date Formatting Across Databases

While this tutorial uses SQLite’s strftime(), here’s how to achieve the same results in other databases:

PostgreSQL with TO_CHAR

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') as formatted FROM orders;
SELECT TO_CHAR(order_date, 'Day, Month DD, YYYY') as readable FROM orders;
SELECT TO_CHAR(order_date, 'HH24:MI:SS') as time_only FROM orders;

PostgreSQL’s format codes are slightly different:

• YYYY instead of %Y
• MM instead of %m
• DD instead of %d
• Day and Month for full names

MySQL with DATE_FORMAT

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') as formatted FROM orders;
SELECT DATE_FORMAT(order_date, '%W, %M %d, %Y') as readable FROM orders;
SELECT DATE_FORMAT(order_date, '%H:%i:%s') as time_only FROM orders;

MySQL uses the same % codes as SQLite but with some additions like %W for full weekday name.

SQL Server with FORMAT

SELECT FORMAT(order_date, 'yyyy-MM-dd') as formatted FROM orders;
SELECT FORMAT(order_date, 'dddd, MMMM dd, yyyy') as readable FROM orders;
SELECT FORMAT(order_date, 'HH:mm:ss') as time_only FROM orders;

SQL Server uses .NET format strings which differ from the Unix-style % codes.

Performance Considerations

Date formatting is generally fast, but keep these tips in mind:

Avoid formatting in WHERE clauses when possible:

-- Slower: formats every row before comparing
SELECT * FROM orders
WHERE strftime('%Y', order_date) = '2026';

-- Faster: compares dates directly
SELECT * FROM orders
WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01';

Pre-compute common formats: If you frequently need the same format, consider storing it in a computed column or view.

Use appropriate indexes: Create indexes on date columns you filter by frequently.

Common Pitfalls

NULL handling

If the date value is NULL, formatting functions return NULL:

SELECT strftime('%Y-%m-%d', NULL);
-- Result: NULL

Use COALESCE to provide a default:

SELECT COALESCE(strftime('%Y-%m-%d', order_date), 'No date') FROM orders;

String vs Date comparisons

Formatted dates are strings, so comparisons work differently:

-- This compares strings, not dates!
-- '2026-02' > '2026-10' is FALSE because '0' < '1' alphabetically
SELECT * FROM sales
WHERE strftime('%Y-%m', sale_date) > '2026-02';

For chronological sorting, YYYY-MM format works correctly because it sorts alphabetically in date order.

Timezone awareness

SQLite’s strftime() works with UTC by default. For timezone-aware formatting in production, consider:

• Storing dates in UTC
• Converting to local time in your application layer
• Using database-specific timezone functions

Working with Date Formats in Beekeeper Studio

If you work with dates regularly, having the right tools makes a difference. Beekeeper Studio offers features that simplify date handling:

• Visual query results: Dates are displayed clearly with formatting options
• Autocomplete: Suggests date functions and format codes as you type
• Multi-database support: Use the same interface for PostgreSQL, MySQL, SQLite, and more
• Query history: Save and reuse your date formatting queries

The free version includes everything you need to master SQL date formatting across different database systems.

Key Takeaways

Date formatting is an essential SQL skill that you’ll use constantly. Here’s what to remember:

• strftime() is your main tool for formatting dates in SQLite, with equivalents in every major database
• Format codes like %Y, %m, %d let you extract and arrange date components
• Combine codes with text to create human-readable displays
• Group by formatted dates for time-based reports (monthly, quarterly, yearly)
• Use day of week (%w) for scheduling and business logic
• Avoid formatting in WHERE clauses for better performance
• Remember NULL handling - formatting NULL returns NULL

Practice with the interactive examples above to build muscle memory for common date formatting patterns. The more you work with dates, the more natural it becomes.

Ready to explore more SQL concepts? Check out our other interactive SQL tutorials to continue building your skills.