DATEFORMAT() in SQL
Converts a date/timestamp expression into a specified string format.
Syntax
Return type
DATEFORMAT() Function Example
What is DATEFORMAT() in SQL?
The DATEFORMAT() function in SQL converts a date or timestamp expression into a specified string format, allowing customization of how date values are displayed. It is commonly used for reporting, user-friendly date formatting, and exporting data in specific formats.
This function is not standard SQL but is available in certain databases:
MySQL: Uses
DATE_FORMAT(date, format)SQL Server: Uses
FORMAT(date, format)PostgreSQL & Oracle: Use
TO_CHAR(date, format)SQLite: Uses
strftime(format, date)
Parameters:
date_expression: Date, datetime, or timestamp value
format_string: Format pattern specifying output format
Example Use Cases:
-- Format date as 'YYYY-MM-DD'
-- Format with custom separators
-- Include time component
Notes:
Behavior: Returns NULL if input is NULL
Performance Considerations: String manipulation may impact performance
Version Info: Format patterns vary by DBMS
Deprecated/Recommended Alternatives: Some DBMS use TO_CHAR() or CONVERT()
Error Handling:
Error: Returns NULL for invalid dates or formats
Recommendation: Validate date and format string before conversion
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
DATE_FORMAT()
DATE_FORMAT(date, '%Y-%m-%d')
Returns NULL
PostgreSQL
TO_CHAR()
TO_CHAR(date, 'YYYY-MM-DD')
Returns NULL
SQL Server
FORMAT() or CONVERT()
FORMAT(date, 'yyyy-MM-dd')
Returns NULL
SQLite
strftime()
strftime('%Y-%m-%d', date)
Returns NULL
BigQuery
FORMAT_DATE()
FORMAT_DATE('%Y-%m-%d', date)
Returns NULL
Snowflake
TO_CHAR()
TO_CHAR(date, 'YYYY-MM-DD')
Returns NULL
Athena
date_format()
date_format(date, '%Y-%m-%d')
Returns NULL
