SQL Date Format: How to Convert and Format Dates

What Is SQL Date Format

SQL date format refers to the way a date or datetime value appears in query output. For example, one database may show 2026-05-26, while a report may need 26-05-2026 or May 26, 2026.

A database usually stores dates in a standard internal format. Formatting changes how the value is shown, converted, or exported. This is useful when building reports, filtering records, preparing dashboards, or converting strings into valid date values.

SQL Date Format by Database

Different databases use different date formatting functions. This is one of the most important things beginners should understand.

DATE_FORMAT() works in MySQL, but not in SQL Server. Similarly, CONVERT() with style codes is common in SQL Server, but not in MySQL. Always check the database before using a date format function.

SQL Date and Time Data Types

Before formatting dates, know what type of value you are working with.

Important: In SQL Server, TIMESTAMP is not a date and time data type. To store date and time values, use DATE, DATETIME, DATETIME2, TIME, or DATETIMEOFFSET.

Advance Your Career with SQLSQL Certification CourseExplore Program

How to Format Dates in SQL Server

In SQL Server, the most common way to format dates is by using CONVERT() with a style code.

SELECT CONVERT(varchar(10), GETDATE(), 23) AS formatted_date;

Output:

2026-05-26

Here, style code 23 returns the date in YYYY-MM-DD format.

You can also use FORMAT() when you need a custom display format.

SELECT FORMAT(GETDATE(), 'dd-MM-yyyy') AS formatted_date;

Output:

26-05-2026

CONVERT() is useful for standard SQL Server date conversions. FORMAT() is useful for custom or more readable date output.

Common SQL Server Date Format Codes

CAST vs CONVERT in SQL

CAST() and CONVERT() both change one data type into another, but they are not exactly the same.

CAST() is simpler and more standard across SQL databases.

SELECT CAST(GETDATE() AS date) AS only_date;

CONVERT() is more flexible in SQL Server because it supports style codes.

SELECT CONVERT(varchar(10), GETDATE(), 103) AS formatted_date;

Use CAST() for basic type conversion. Use CONVERT() in SQL Server when you need a specific date display format.

Start Your Journey to Data MasterySQL Certification CourseExplore Program

How to Format Dates in MySQL

In MySQL, use DATE_FORMAT() to display dates in a specific format.

SELECT DATE_FORMAT(order_date, '%Y-%m-%d') AS formatted_date
FROM orders;

Common MySQL date format specifiers include:

To convert a string into a date in MySQL, use STR_TO_DATE().

SELECT STR_TO_DATE('26-05-2026', '%d-%m-%Y') AS converted_date;

PostgreSQL and Oracle Date Formatting

PostgreSQL example:

SELECT TO_CHAR(order_date, 'YYYY-MM-DD') AS formatted_date
FROM orders;

Oracle example:

SELECT TO_CHAR(order_date, 'DD-MM-YYYY') AS formatted_date
FROM orders;

Both examples use TO_CHAR() to create readable date output for reports or dashboards.

SQL Date Functions You Should Know

SQL date functions help you extract, calculate, and manipulate date values.

Example in SQL Server:

SELECT YEAR(order_date) AS order_year,
      MONTH(order_date) AS order_month
FROM orders;

Learn in-demand skills including SQL, Database Management System DBMS, RealWorld Problem Solving, MySQL, and Data Analysis with our SQL Certification Training.

Common SQL Date Format Errors

SQL date format errors often happen when the query syntax, data type, or date value does not match the database being used. Here are the most common mistakes to watch for.

• Using the wrong function for the wrong database: DATE_FORMAT() works in MySQL, but not in SQL Server. Similarly, SQL Server CONVERT() style codes do not work in MySQL.
• Storing dates as text: Dates stored as varchar values can cause sorting and filtering issues. Use proper date or datetime data types, then format them only for display.
• Using two-digit years: A value like 26 can be interpreted differently across systems. Use four-digit years, such as 2026, for safer and clearer results.

Best Practices for SQL Date Formatting

The best way to handle SQL date formatting is to store dates correctly first, then format them only when the output needs to be displayed, exported, or reported.

• Use date and datetime data types instead of storing dates as strings. Format dates only when showing them in reports, dashboards, or application screens.
• Keep database syntax separate. Use CONVERT() or FORMAT() in SQL Server, DATE_FORMAT() in MySQL, and TO_CHAR() in PostgreSQL or Oracle.
• For clean and sortable output, use YYYY-MM-DD. For global applications, use time zone-aware data types and define whether dates are stored in local time or UTC.

Conclusion

SQL date format is essential when working with reports, dashboards, filters, and application output. The main rule is simple: store dates in the correct date or datetime data type, then use the right formatting function for your database. Since SQL Server, MySQL, PostgreSQL, and Oracle handle date formatting differently, keeping the syntax separate helps prevent errors and improve query reliability. If you want to build stronger database and querying skills, Simplilearn’s SQL Certification Course can help you learn SQL fundamentals, joins, functions, data filtering, and real-world query writing through structured practice. 

Key Takeaways

• SQL date format controls how date and datetime values appear in query output.
• SQL Server commonly uses CONVERT() and FORMAT().
• MySQL uses DATE_FORMAT() to display dates and STR_TO_DATE() to convert strings.
• PostgreSQL and Oracle use TO_CHAR() for date and timestamp formatting.

FAQs

1. How do you convert a string to a date format in SQL?

You can convert a string to a date format using database-specific functions. In SQL Server, use CAST(), CONVERT(), or TRY_CONVERT(). In MySQL, use STR_TO_DATE(). Make sure the string pattern matches the expected date format to avoid conversion errors.

2. What is the difference between TIMESTAMP and DATETIME in SQL?

DATETIME stores date and time values. TIMESTAMP works differently across databases. In SQL Server, TIMESTAMP is not a date or time data type. It is a deprecated synonym for ROWVERSION. Use DATETIME, DATETIME2, or DATETIMEOFFSET for date and time values.

3. How do you handle time zones in SQL datetime values?

Use time zone-aware data types when offsets matter. In SQL Server, DATETIMEOFFSET stores the date, time, and UTC offset. For global applications, store date and time values in UTC, then convert them to local time only when displaying reports, dashboards, or application output.