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 stringTO_DATE(string, format)- parses string into date
MySQL:
DATE_FORMAT(date_column, format)- converts date to stringSTR_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.