Course contentsShow
← Data Science
Lesson 1041 of 2,14526. Advanced SQLFree lesson

Formatting and Parsing Dates

Converting between date types and strings using TO_CHAR, TO_DATE, DATE_FORMAT, and STRFTIME functions.

Formatting and Parsing Dates

You'll learn how to convert between date/time types and strings using built-in SQL formatting functions.

The Core Idea

Dates stored in databases are powerful for calculations, but often you need to display them in human-friendly formats ("January 15, 2024" instead of "2024-01-15") or convert user-input strings into proper date types. SQL provides specialized functions to translate between these representations.

Think of it like currency: internally you store amounts as numbers, but when presenting them you format with dollar signs and commas. Similarly, dates are stored efficiently as date types but displayed in readable formats.

Key Functions by Database

Different SQL databases use different function names for the same operations:

PostgreSQL:

  • TO_CHAR(date_column, format) - converts date to formatted string
  • TO_DATE(string, format) - parses string into date

MySQL:

  • DATE_FORMAT(date_column, format) - converts date to string
  • STR_TO_DATE(string, format) - parses string into date

SQLite:

  • STRFTIME(format, date_column) - converts date to string

Common Format Codes

While syntax varies, concepts remain similar:

  • 'YYYY' or '%Y' for 4-digit year
  • 'MM' or '%m' for 2-digit month
  • 'DD' or '%d' for day
  • 'Mon' or '%b' for abbreviated month name
-- PostgreSQL: Format as "Month DD, YYYY"
TO_CHAR(order_date, 'Month DD, YYYY')

-- MySQL: Same result
DATE_FORMAT(order_date, '%M %d, %Y')

-- Parse "2024-03-15" string into date
TO_DATE('2024-03-15', 'YYYY-MM-DD')  -- PostgreSQL
STR_TO_DATE('2024-03-15', '%Y-%m-%d')  -- MySQL

Key Takeaway: Use database-specific formatting functions (TO_CHAR, DATE_FORMAT, STRFTIME) to convert dates to readable strings, and parsing functions (TO_DATE, STR_TO_DATE) to convert strings back into date types for calculations.