In this article, we explore the SQL date format. We’ll deal with how standard and custom date formatting work in four of the most popular SQL flavors.

Databases don’t only hold numerical or textual data. Dates are also common, as the time dimension is important for understanding many data.

Anyone working with dates knows that sometimes they are not formatted the way you want them to be. So, you’ll need to be aware of the various date formats in SQL and how to format them to suit you.

Let me kick off, with the fundamentals, and then I’ll guide you step-by-step through SQL date formatting.

What are SQL Date and Time Formats?

Before learning how to handle them, you first need to know what they are.

SQL date and time formats refer to the standards and conventions used to represent temporal data within a database. These formats allow you to store, retrieve, and manipulate dates and times in a consistent manner.

This is one of the toughest data to handle in databases.  It requires accuracy while bearing in mind different locations and the local format of writing date and time.

There are several distinct types of such data, each serving a different purpose.

Date and Time Data Types in SQL

There are four typical date and time data types in SQL.

1. DATE

The DATE data type is your go-to choice for storing dates without time of day. It encompasses year, month, and day components, making it ideal for birthdays, anniversaries, or any event that doesn't require time precision.

In practice, querying a DATE column could help you extract all records for events occurring on a specific day or perform date arithmetic, such as calculating age from a birthdate.

2. TIME

When your interest lies strictly in the time of day, the TIME data type is for you. It records hours, minutes, seconds, and even fractional seconds, depending on the database system. This precision is particularly useful for logging event times, durations, or operating hours.

SQL queries utilizing TIME can efficiently compare, add, or subtract time values, enabling applications like calculating time differences or determining if a specific time falls within a given interval.

3. DATETIME

Merge the above two data types and you get DATETIME. It’s a data type that, obviously, captures both date and time in a single column. This combination comes in handy for timestamps, appointments, and historical records where the exact moment an event occurred is crucial.

4. TIMESTAMP

The TIMESTAMP data type is akin to DATETIME but often comes with special behavior related to time zones and automatic updates. In many SQL databases, TIMESTAMP columns are automatically updated to the current date and time when a row is modified. This feature makes it exceptionally suited for tracking changes to records, auditing, and versioning.

It's important to note that TIMESTAMP values are usually stored in UTC, with conversions happening transparently based on the database or session time zone settings.

How to Format Dates in SQL: A Step-by-Step Guide

Each database engine has its own specific function designed to format date and time. Apart from this, it’s also possible to custom format dates.

I’ll first show you the built-in functions for the four most popular databases: PostgreSQL, MySQL, SQL Server, and Oracle. Then, I’ll move on to custom formatting.

Using Built-in SQL Functions for Date Formatting

1. PostgreSQL

PostgreSQL uses the TO_CHAR() function to format date and time. With it, you can convert date, time, timestamp, and interval values to a string formatted in a certain way.

Syntax

The TO_CHAR() function’s syntax is given below.

TO_CHAR(date_or_time_value, 'format_string');

Here’s the explanation:

• date_or_time_value: The date, time, or timestamp column or expression you want to format. Note: If you’re converting timestamp or interval, include TIMESTAMP or INTERVAL keywords before date_or_time_value)
• format_string: Specifies the format of the output. This string can include various format specifiers for different components of the date or time, such as:
  • YYYY - 4-digit year
  • MM - 2-digit month
  • DD - 2-digit day of the month
  • Day - Full name of the day
  • Mon - Abbreviated month name
  • HH24 - Hours (00 to 23)
  • MI - Minutes (00 to 59)
  • SS - Seconds (00 to 59)

Example

Say you want to write timestamp '1982-11-27 10:45:41' as '27-11-1982'. In PostgreSQL, you should write this code.

SELECT TO_CHAR(TIMESTAMP '1982-11-27 10:45:41', 'DD-MM-YYYY') AS formatted_datetime;

In the TO_CHAR() function, you write an example date or time in the single quotes. Since the example is a timestamp, it must be preceded by the keyword TIMESTAMP. The second argument in the function is the output format, written in single quotes. In our case, the format is DD-MM-YYYY.

Voilà!

2. MySQL

MySQL uses the DATE_FORMAT() function to do the same thing shown above. Similar to PostgreSQL's TO_CHAR(), it takes a date or datetime value and a format string as arguments.

Syntax

Here’s the DATE_FORMAT() syntax.

DATE_FORMAT(date, format);

In the above syntax

• date: The date or datetime expression that you want to format.
• format: The string that defines how the output should be formatted. The format string can contain special format specifiers defining a part of the date or time, each starting with a %. Here are some format specifiers you’ll probably use most often:
  • %Y - 4-digit year
  • %y - 2-digit year
  • %m - Month, numeric (00 to 12)
  • %d - Day of the month (00 to 31)
  • %H - Hour (00 to 23)
  • %i - Minutes (00 to 59)
  • %s - Seconds (00 to 59)
  • %M - Month name (January to December)
  • %W - Day name (Sunday to Saturday)

Example

The same code I wrote in PostgreSQL would look like this in MySQL.

SELECT DATE_FORMAT('1982-11-27 10:45:41', '%d-%m-%Y') AS formatted_datetime;

As usual, the timestamp is written in single quotes without any keyword needed to specify it’s a timestamp. The second argument is the output format. The DD-MM-YYYY format in MySQL is specified as '%d-%m-%Y'.

Here’s the output.

3. SQL Server

In SQL Server, the FORMAT() function is used to format date and time values. This function is somewhat similar to those in PostgreSQL and MySQL but uses .NET's date and time format strings.

Syntax

The syntax for this function is:

FORMAT(value, format [, culture]);

Here’s the explanation of the function’s arguments.

• value: The date, time, or numeric value you want to format.
• format: A .NET Framework format string that dictates how the output is formatted. For dates and times, this string can specify year, month, day, hour, minute, second, etc., in various display formats.
• culture (optional): A string that specifies the culture to use for formatting. This affects how dates, times, and numbers are displayed (e.g., en-US for the United States, fr-FR for France). If omitted, the default culture of the current session is used.

Here are the most commonly used format specifiers.

• d: Short date pattern.
• D: Long date pattern.
• dd: Day of the month, from 01 through 31.
• ddd: Abbreviated name of the day of the week.
• dddd: Full name of the day of the week.
• MM: Month, from 01 through 12.
• MMM: Abbreviated name of the month.
• MMMM: Full name of the month.
• yyyy: Year, four digits.
• HH: Hour in 24-hour format, from 00 through 23.
• mm: Minute, from 00 through 59.
• ss: Second, from 00 through 59.

Example

The previous example written in T-SQL looks like this.

SELECT FORMAT(CAST('1982-11-27 10:45:41' AS DATETIME), 'dd-MM-yyyy') AS formatted_datetime;

Unfortunately, in SQL, you must use the CAST() or CONVERT() function for the query to work. This is because the FORMAT() function does not implicitly convert a string literal to a datetime type, even when the string is in ISO 8601 format.

I used CAST() in the above query to convert to datetime. Then, as usual, the second argument is the output format. Our desired output in SQL Server is written as 'dd-MM-yyyy' because 'mm' is minutes, while 'MM' is a month.

The result is the same as in earlier examples.

If you want to use the optional culture argument, you could do it like this.

SELECT FORMAT(CAST('1982-11-27 10:45:41' AS DATETIME), 'D', 'HR') AS formatted_date_in_croatian;

I changed the format to 'D', which stands for a long date pattern that allows for localization of the format. The 'culture' argument is HR, which stands for Croatia (‘Hrvatska’, in Croatian).

The code returns the date with the month name written in Croatian.

4. Oracle

Oracle also uses the TO_CHAR() function to format dates and times, much like PostgreSQL. However, the syntax and some format models differ slightly due to Oracle's distinct SQL implementation.

Syntax

The syntax in Oracle is as follows.

TO_CHAR(date_value, format_mask, 'nls_language');

The arguments’ meaning is:

• date_value: The date or datetime expression that you want to convert to a string.
• format_mask (optional): The format that defines how the output is formatted. This string can include various format elements for different date or time components.
• nls_language (optional): It specifies the language in which month and day names and abbreviations are returned. This parameter is useful for returning date components in a specific language.

Here are the most common format specifiers in Oracle.

• YYYY: Four-digit year.
• MM: Two-digit month.
• DD: Two-digit day of the month.
• HH24: The hour (00 to 23).
• MI: Minutes (00 to 59).
• SS: Seconds (00 to 59).

Example

The code from the previous examples should be written like this in Oracle.

SELECT TO_CHAR(TO_DATE('1982-11-27 10:45:41', 'YYYY-MM-DD HH24:MI:SS'), 'DD-MM-YYYY') AS formatted_datetime FROM DUAL;

Like SQL Server, Oracle does not implicitly convert a string literal to a datetime data type. So, I had to use TO_DATE() for this conversion. The first argument in this function is a string, and the second is the output format representing the datetime format.

After that, I can specify the formatting of this datetime, which is 'DD-MM-YYYY'.

The 'FROM DUAL' part of the code is specific to Oracle. The DUAL table is a special one-row, one-column table present by default in all Oracle databases. Its primary purpose is to provide a convenient means to select a pseudo-column such as SYSDATE or to perform calculations and access built-in functions without needing to select from an actual table.

Here’s the code output.

Custom Date Formatting in SQL

Built-in functions cover most needs for date formatting. However, on occasions they don’t meet specific requirements, the custom date formatting comes into play. It often involves manipulating date parts (year, month, day, etc.) and assembling them in unique ways, using conditional logic, string manipulation, and other techniques.

Custom date formatting usually means displaying dates in a non-standard format, performing language-specific transformations not supported by default, or incorporating logic that alters the output based on specific conditions.

Let’s see how this is done in all four databases.

1. PostgreSQL Custom Date Formatting

When turning dates into custom formats in PostgreSQL, you would typically apply these approaches.

• String Functions and Operators: Combine date parts using string functions (|| for concatenation, LPAD(), RPAD()) and date part extraction functions (EXTRACT(), DATE_PART()).
• Conditional Expressions: Use CASE or COALESCE() for conditional logic in formatting.

Example

Here’s an example of custom date formatting in PostgreSQL.

SELECT
  'Day of the Year: ' || EXTRACT(DOY FROM CURRENT_DATE) || ', Week of the Year: ' || EXTRACT(WEEK FROM CURRENT_DATE) AS custom_format;

The EXTRACT() function retrieves the day of the year (DOY) from the current date (returned by the function of the same name).

The following application of the EXTRACT() function returns the week from the current date.

These two values are concatenated (using ||) with the strings that describe them.

And the final result is this.

2. MySQL Custom Date Formatting

In MySQL, the following functions are used to format custom dates.

• String Functions: Use CONCAT(), LPAD(), and other string manipulation functions to assemble custom formats.
• Date and Time Functions: YEAR(), MONTH(), DAY(), etc., to extract specific date parts.
• Control Flow Functions: CASE, IF(), COALESCE() for conditional formatting.

Example

When translated to MySQL, the code from the previous example looks like this.

SELECT CONCAT('Day of the Year: ', DAYOFYEAR(CURDATE()), ', Week of the Year: ', WEEK(CURDATE())) AS custom_format;

Instead of ||, MySQL uses the CONCAT() function to concatenate strings.

To extract the day of the year from the current date, I use the DAYOFYEAR() and CURDATE() functions. For the week of the year, it’s the WEEK() function.

Here’s the output.

3. SQL Server Custom Date Formatting

Custom formatting dates in SQL Server requires you to use the following techniques.

• String Functions: CONCAT(), FORMAT(), LEFT(), RIGHT() for assembling custom date formats.
• Date and Time Functions: DATEPART(), YEAR(), MONTH(), DAY() to extract date parts.
• Conditional Logic: Use CASE statements for custom logic in formatting.

Example

In SQLServer, the code from the previous examples is written like this.

SELECT CONCAT('Day of the Year: ', DATEPART(DAYOFYEAR, GETDATE()), ', Week of the Year: ', DATEPART(WEEK, GETDATE())) AS custom_format;

SQL Server, just like MySQL, uses CONCAT() for concatenation. To get the day and week of the year, you need the function DATEPART(). In this function, the first argument is for declaring what part of the date you need. In these cases, it’s DAYOFYEAR and WEEK, respectively.

The current datetime is retrieved with the function GETDATE().

4. Oracle Custom Date Formatting

Custom date formatting in Oracle is done using these functions.

• String Functions and Operators: Use concatenation operator ||, LPAD(), RPAD(), and others for assembling custom formats.
• Date Functions: EXTRACT() to retrieve specific parts of a date.
• Conditional Expressions: Utilize CASE, DECODE(), or NVL() for conditional formatting.

Example

Let’s rewrite the previous code for Oracle.

SELECT 'Day of the Year: ' || TO_CHAR(SYSDATE, 'DDD') || ', Week of the Year: ' || TO_CHAR(SYSDATE, 'IW') AS custom_format
FROM DUAL;

Like in PostgreSQL, I use '||' for concatenation in Oracle, too. For extracting the parts of the date, there’s the TO_CHAR() function. The 'DDD' format is for the day of the year, while 'IW' is for the week.

The SYSDATE function returns the current date.

Here’s the output.

Converting Strings to Dates and Vice Versa

Converting strings to dates and vice versa is a common requirement in database management, as it allows for the manipulation and comparison of date values stored in different formats. Each major SQL database system—PostgreSQL, MySQL, SQL Server, and Oracle—provides specific functions for these conversions.

Let’s see how this works in different databases. I’ll first talk about database-specific functions.

Database-Specific Conversion Functions

1. PostgreSQL

The function TO_DATE() is used to convert strings to dates in PostgreSQL.

To do the opposite, use the TO_CHAR() function, which you already know to use. Yes, you used it to format dates. But, in that process, dates are converted to a string.

Syntax

The syntax of TO_DATE() is very similar to that of TO_CHAR(). I’ll discuss only the former, as I’ve already covered the latter.

TO_DATE(text, format);

The arguments of the function are:

• text: The textual representation of the date you want to convert.
• format: Specifies the format of the string written in the text argument. This format uses specific pattern codes to denote the year, month, day, etc., in the text string. The format specifiers are the same as in the TO_CHAR() function.

Example

Say you have a date saved as a string in the format ‘Day-Month-Year’ (e.g., ‘27-November-1982’), and you want to convert it to a date. You would use the TO_DATE() function like this:

SELECT TO_DATE('27-November-1982', 'DD-Month-YYYY') AS converted_date;

The first argument is the string in single quotes. The second argument is the desired date format, also written in single quotes. This date format must reflect the format of the string.

Here’s the output.

2. MySQL

If you wish to convert strings to dates in MySQL, you need to use the STR_TO_DATE() function.

Again, for the opposite conversion, use the function you used for formatting dates: DATE_FORMAT().

Syntax

The STR_TO_DATE() syntax is as follows.

STR_TO_DATE(str, format);

As in PostgreSQL, the first argument is the string; the second is the output date format.

Example

Let’s use the previous example. In MySQL, it would look like this.

SELECT STR_TO_DATE('27-November-1982', '%d-%M-%Y') AS converted_date;

Same as in the PostgreSQL example, the output format must reflect the string format.

3. SQL Server

SQL Server technically uses three functions to convert strings to dates:

• CAST()
• CONVERT()
• PARSE()

CAST() is an SQL standard, while CONVERT() is an SQL Server-specific function. Both functions convert data from one data type to another. This obviously applies to converting strings to dates and vice versa.

PARSE() provides culture-specific parsing of the date string, which is helpful in converting strings in specific locales.

You already know how to use FORMAT() to convert a date to a string. You can also use CAST() and CONVERT() here.

Syntax

Let’s go through the syntax of the three functions.

CAST() Syntax

Here’s the CAST() function syntax.

CAST(string AS data_type);

In the function, you write the string, then follow it with the keyword AS and the desired data type.

CONVERT() Syntax

For the CONVERT() function, the syntax is as follows.

CONVERT(DATE, string, style);

In this instance, the desired data type comes first, followed by the string. The style argument is used to determine the specific style of a date and time. All the styles in SQL Server are given here.

PARSE() Syntax

Lastly, the PARSE() function has the following syntax.

PARSE(string AS DATE USING culture);

The syntax is very similar to CAST(), only PARSE() allows the culture argument to show dates in the local style.

Example

Let’s use the previous examples and show them with all three functions in SQL Server.

Since CAST() and CONVERT() don’t allow for explicit specification of the format, these two functions need to be used in combination with FORMAT().

SELECT FORMAT(CAST('27-November-1982' AS DATE), 'yyyy-MM-dd') AS converted_date;

First, you cast the string into a date. Then, you embed this function in FORMAT() and specify the desired format.

Converting with CONVERT() works the same way.

SELECT FORMAT(CONVERT(DATE, '27-November-1982'), 'dd-MM-yyyy') AS converted_date;

To do the same with PARSE(), you should write the code like this. The culture used in PARSE() should match the language of the month in the input string. Since 'November' is in English, you'd typically use 'en-US' or another English-based culture.

SELECT FORMAT(PARSE('27-November-1982' AS DATE USING 'en-US'), 'dd-MM-yyyy') AS formatted_date;

CAST(): SQL Standard Conversion Function

One conversion function exists among the databases: CAST().

I previously mentioned it within SQL Server simply because the database’s FORMAT() function doesn’t implicitly convert string to date (and vice versa)—unlike the date formatting functions in other databases—so CAST() had to be used in conjunction with it.

CAST() is a function used for general data type conversion, i.e., not limited to date or time data types.

Since it’s not specialized for date and time conversion, CAST() can be limiting when it comes to casting a string to an exact date format.

The additional difference compared to the designated date conversion functions is that CAST() only allows data conversion, but it’s not possible to specify the output format.

After all that, a big, big recommendation: Use the database-specific functions when converting strings to dates and vice versa, and use CAST() for other data type conversions.

Note: While MySQL and Oracle also support CONVERT(), they allow it only for converting between two different character sets. It’s used for date conversion only in SQL Server; it’s not a standard SQL function, so I won’t mention it anymore in the context of date conversions.

Best Practices for SQL Date Formatting

Adhering to these best practices is necessary if you want to ensure consistency and clarity across the databases, systems, and locales.

1. Use Standard Date Formats Where Possible

• ISO 8601 Format: Adopt the ISO 8601 standard (YYYY-MM-DD for dates, YYYY-MM-DDTHH:MM:SS for timestamps) for internal storage and manipulation. This format is universally recognized and avoids ambiguity between US (MM-DD-YYYY) and European (DD-MM-YYYY) date formats.
• Consistency: Stick to a consistent date format within your database and application. That way, you’ll avoid confusion and errors in interpreting dates. Imagine the confusion if your database shows the date as 05-04-2023 while the application shows it as 04-05-2023. What’s a day, and what’s a month? Who knows!

2. Use Database-Specific Formatting Functions for Presentation

• Utilize built-in functions: Use functions such as TO_DATE(), TO_CHAR(), STR_TO_DATE(), DATE_FORMAT(), and FORMAT() for converting and formatting dates when preparing data for user interfaces or external reports. This ensures that dates are formatted according to user expectations.
• Dialect-specific: Be aware of the functions specific to your SQL dialect and use those functions when needed.

3. Account for Time Zones and Locale

• Time Zone Awareness: You should store timestamps in UTC to avoid confusion across different time zones, especially in web applications used by customers worldwide. Knowing that all timestamps are in UTC, it’s easy to convert them to local time zones when necessary.
• Locale-Specific Formatting: However, when your dates are displayed to the users, they should be formatted according to the local conventions. Countries have different start times for the week, month names, etc.

4. Optimize Performance for Date Formatting Operations

• Performance Considerations: Frequent conversion and date formatting can impact SQL query performance. To avoid that, perform date formatting at the application level or cache frequently used formatted dates.
• Indexing: Be aware that formatting dates in WHERE clauses or JOIN conditions (e.g., using TO_CHAR() on a date column in a condition) can prevent the use of indexes. Keep date columns in their native types for any operations that require indexing.

5. Validate and Sanitize Input Dates

• Validation: Always validate date inputs in application forms or APIs to ensure they match the expected format before inserting them into the database.
• Sanitization: Convert or reformat user-provided dates into a standardized format for storage to prevent SQL injection attacks and ensure data integrity.

Common Mistakes Beginners Make When Formatting Dates in SQL

Working with dates can be tricky, especially for beginners. Here are some common mistakes made when formatting dates in SQL. They usually stem from not respecting the best practices mentioned earlier.

1. Ignoring Locale and Time Zone Differences

• Mistake: Not accounting for time zones when storing and displaying dates. This leads to wrong dates being shown to users in different locations.
• Solution: Store dates in UTC and convert them to the local time zone when being displayed.

2. Using Inconsistent Date Formats

• Mistake: Not sticking with the uniform date format, causing confusion and errors when interpreting dates.
• Solution: Stick to a single date format for storage, preferably ISO 8601. Again, you can convert the dates according to the locale when showing dates to users.

3. Misusing Date Formatting Functions

• Mistake: Incorrectly using date formatting functions, e.g., using FORMAT() when CONVERT() or CAST() is needed.
• Solution: Use the proper functions, obviously.

4. Performing Operations on Formatted Dates

• Mistake: Attempting to perform calculations (adding, subtraction…) or comparisons on dates that have been converted to strings.
• Solution: Do calculations before formatting dates as strings. If it’s already formatted as a string, convert it back to a date type and then do the calculations.

5. Overlooking Performance Implications

• Mistake: Excessive conversion and formatting of dates in SQL queries, increasing the query execution time.
• Solution: Convert and format dates in SELECT only when needed. In all other cases, consider date formatting at the application level.

6. Relying on Implicit Date Conversions

• Mistake: Relying on the database to implicitly convert between strings and dates. This can be problematic if the database's default date format settings change.
• Solution: Always explicitly state the date format when converting.

7. Incorrectly Formatting Dates in WHERE Clauses

• Mistake: As you already learned in the previous section, using formatted dates in WHERE clauses can prevent the use of indexes. In turn, this degrades your query performance.
• Solution: Keep dates in their native format for filtering and use parameterized queries to avoid formatting issues.

Real-World Examples of SQL Data Formatting

Now, let’s see how what we talked about date formatting works in real-world examples. First, let me show you an example of the built-in functions. Then the following example will be about custom date formatting.

Example: Date Formatting Using the Built-in Functions

I’ll solve this Deloitte interview question in all four SQL dialects.

Link to the question: https://platform.stratascratch.com/coding/2162-top-3-year-month-sales

The question wants you to find the top three monthly sales, with the months formatted as YYYY-MM.

You’re given the table fct_customer_sales.

1. PostgreSQL Solution: Using TO_CHAR()

SQL CTE is a neat way to solve this question. Let’s write it first as a regular SELECT statement and then convert it to a CTE.

In SELECT, I use the TO_CHAR() to function format data in the column order_date. This is per the question’s request, as this data needs to be in a 'YYYY-MM' format.

In addition, there’s the SUM() function to sum all the sales for each month and RANK() to rank the months by the monthly sale sum.

SELECT TO_CHAR(order_date, 'YYYY-MM') AS sales_month,
       SUM(order_value) AS total_sales,
       RANK() OVER(ORDER BY SUM(order_value) DESC) AS rnk
FROM fct_customer_sales
GROUP BY 1;

The query returns the following table.

Now, let’s turn the query into a CTE and reference it with another query.

WITH cte AS (
  SELECT TO_CHAR(order_date, 'YYYY-MM') AS sales_month,
         SUM(order_value) AS total_sales,
         RANK() OVER(ORDER BY SUM(order_value) DESC) AS rnk
  FROM fct_customer_sales
  GROUP BY 1
)

SELECT sales_month,
       total_sales
FROM cte
WHERE rnk < 4;

This second query is a simple SELECT statement that outputs the top three ranked months and their sales, as shown below.

Here’s the output.

2. MySQL Solution: Using DATE_FORMAT()

The MySQL solution is the same as PostgreSQL; the only difference is we use DATE_FORMAT() instead of TO_CHAR(), and the format is written differently in the formatting function.

WITH cte as (
    SELECT DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
           SUM(order_value) AS total_sales,
           RANK() OVER (ORDER BY SUM(order_value) DESC) as rnk 
    FROM fct_customer_sales
    GROUP BY 1
)
    
SELECT sales_month, 
       total_sales
FROM cte 
WHERE rnk < 4;

Run the code to get the same output.

3. MS SQL Server Solution: Using FORMAT()

This solution is, again, very similar to the previous two. In SQL Server, we use FORMAT() for date formatting, and we specify the required format as 'yyyy-MM'. Additionally, we need to use FORMAT() again in GROUP BY, as SQL Server is less flexible than PostgreSQL and MySQL, so it doesn’t allow referencing the alias used in SELECT directly in GROUP BY.

WITH cte as (
    SELECT FORMAT(order_date, 'yyyy-MM') AS sales_month,
           SUM(order_value) AS total_sales,
           RANK() OVER (ORDER BY SUM(order_value) DESC) as rnk 
    FROM fct_customer_sales
    GROUP BY FORMAT(order_date, 'yyyy-MM')
)
    
SELECT sales_month, 
       total_sales
FROM cte 
WHERE rnk < 4;

This is what the code returns.

4. Oracle Solution: Using TO_CHAR()

The solution in Oracle is basically the same as in PostgreSQL, as they use the same function with the same syntax: TO_CHAR().

WITH cte AS (
    SELECT TO_CHAR(order_date, 'YYYY-MM') AS sales_month,
           SUM(order_value) AS total_sales,
           RANK() OVER (ORDER BY SUM(order_value) DESC) AS rnk
    FROM fct_customer_sales
    GROUP BY TO_CHAR(order_date, 'YYYY-MM')
)

SELECT sales_month, 
       total_sales
FROM cte
WHERE rnk < 4

Here’s the output.

Example: Custom Date Formatting

To see how custom date formatting works in four SQL dialects, let’s solve this Amazon question.

Link to the question: https://platform.stratascratch.com/coding/10363-weekly-orders-report

The question requires you to create a report that will show the total number of orders for each week in the first quarter of 2023.

To calculate this, you get to work with the orders_analysis table.

1. PostgreSQL Custom Date Formatting Solution

The solution, like in the first example, utilizes the CTE. This time, it’s to show the order quantity for each week. In the CTE, the DATE_TRUNC() function is used to adjust the data in the column week to the start of its week. This is because we don’t know if the data in this column shows the first, the last, or any other day of the week.

Additionally, the EXTRACT() function is used two times in WHERE. The first finds the quarter of each week, and the second finds the year. This allows us to include only the data from the first quarter of 2023.

The SELECT query that references the CTE takes weeks and sums the number of orders.

WITH filtered_orders AS (
    SELECT DATE_TRUNC('week', week::DATE) AS week_start,
           quantity 
    FROM orders_analysis
    WHERE EXTRACT(QUARTER FROM week::DATE) = 1 AND EXTRACT(YEAR FROM week::DATE) = 2023
)

SELECT week_start AS week, 
       SUM(quantity) AS total_quantity
FROM filtered_orders
GROUP BY week_start
ORDER BY week_start;

Run the code to get this result.

2. MySQL Custom Date Formatting Solution

The MySQL solution has the same structure only it uses different function to achieve the same thing, namely DATE_FORMAT(), INTERVAL(), WEEKDAY(), QUARTER(), and YEAR().

WITH filtered_orders AS (
    SELECT DATE_FORMAT(week, '%Y-%m-%d') - INTERVAL (WEEKDAY(week) + 1) DAY AS week_start,
           quantity  
    FROM orders_analysis
    WHERE QUARTER(week) = 1 AND YEAR(week) = 2023 
)

SELECT DATE_FORMAT(week_start, '%Y-%m-%d') AS week, 
       SUM(quantity) AS total_quantity
FROM filtered_orders
GROUP BY week_start
ORDER BY week_start;

The above code returns this result.

3. SQL Server Custom Date Formatting Solution

Again, for SQL Server, it’s the same story, only using different functions: DATEADD(), DATEDIFF(), and DATEPART().

WITH filtered_orders AS (
    SELECT DATEADD(wk, DATEDIFF(wk, 0, week), 0) AS week_start,
           quantity 
    FROM orders_analysis
    WHERE DATEPART(QUARTER, week) = 1 AND DATEPART(YEAR, week) = 2023
)

SELECT week_start AS week, 
       SUM(quantity) AS total_quantity
FROM filtered_orders
GROUP BY week_start
ORDER BY week_start;

This is the code output.

4. Oracle Custom Date Formatting Solution

When you look at the official Oracle solution, you’ll see that the week column isn't adjusted to the beginning of each week. Luckily, the data in the week column really does show the first day of the week, so the solution below works.

SELECT week AS week_start, 
       SUM(quantity) AS total_quantity
FROM orders_analysis
WHERE week >= '2023-01-01' AND week < '2023-04-01'
GROUP BY week
ORDER BY week

However, it’s always safer to cover the edge case, i.e., the possibility you can’t look at the table’s data and see if the dates are the first day of each week.

The query below returns the same result. However, it uses TRUNC() with the 'IW' format model to truncate the date to the first day of the ISO week.

It also uses EXTRACT() in WHERE, just like the PostgreSQL solution.

WITH filtered_orders AS (
    SELECT TRUNC(week, 'IW') AS week_start,
           quantity 
    FROM orders_analysis
    WHERE EXTRACT(QUARTER FROM week) = 1 AND EXTRACT(YEAR FROM week) = 2023
)

SELECT week_start AS week, 
       SUM(quantity) AS total_quantity
FROM filtered_orders
GROUP BY week_start
ORDER BY week_start;

Conclusion

This article should be a solid foundation for learning further about dates in SQL. I covered built-in functions and custom date formatting in four SQL flavors. You learned about the functions’ purpose and syntax on simple examples.

Then, we rounded everything off with two real-world examples, where you could see how SQL date formatting works within more complex SQL queries.

If your job requires formatting dates or you’re preparing for SQL interview questions, our coding questions section is perfect for furthering your knowledge on this topic.