SQL Server Date Formatting

by

In this comprehensive article, I’ll walk you through everything you need to know about SQL Server date formatting—from basic conversions to advanced techniques, followed by the best practices.

SQL Server Date Formatting

Before diving into the specifics, let’s understand why date formatting is so crucial. The United States follows the MM/DD/YYYY format (06/10/2025), whereas many other countries use the DD/MM/YYYY format (10/06/2025). This difference alone can lead to significant issues in data interpretation.

SQL Server stores dates in a standardized internal format, but how you display them to users is crucial. Proper date formatting ensures:

  • Data clarity for end-users
  • Consistency across applications
  • Accurate data exchange between systems
  • Compliance with regional standards

Let’s discuss all the approaches to formatting dates in SQL Server.

Approach 1: Using the CONVERT Function

The CONVERT function is one of the oldest and most reliable methods for formatting dates in SQL Server. It uses style codes to specify different date formats.

Syntax:

CONVERT(data_type(length), expression, style)

Where the style parameter is an integer that defines the date format.

Common CONVERT Style Codes for US Date Formats

Style CodeFormatExample Output
101MM/DD/YYYY06/10/2025
110MM-DD-YYYY06-10-2025
111YYYY/MM/DD2025/06/10
1MM/DD/YY06/10/25
107Mon DD, YYYYJun 10, 2025

Example 1

We can use the query below to format the date in the US Date Format.

SELECT CONVERT(VARCHAR, GETDATE(), 101) AS 'US Date Format';

After executing the query, I got the expected output as shown in the screenshot below.

SQL Server Date Formatting

Example 2

The query below formats the date in the Month Day, Year Format.

SELECT CONVERT(VARCHAR, GETDATE(), 107) AS 'Month Day, Year Format';

After executing the above query, I got the expected output as shown in the screenshot below.

SQL Date Formatting

SQL Server provides numerous date formats that you can use with the CONVERT function.

Approach 2: Using the FORMAT Function (SQL Server 2012 and Later)

The FORMAT function, introduced in SQL Server 2012, offers a more intuitive approach to date formatting. Instead of using the style codes, you can specify the exact format you want.

Syntax:

FORMAT(value, format [, culture])

Common Format Strings

Format StringDescriptionExample Output
‘d’Short date pattern6/10/2025
‘D’Long date patternTuesday, June 10, 2025
‘M’Month day patternJune 10
‘Y’Year month patternJune 2025
‘MM/dd/yyyy’Custom format06/10/2025

Example 1

We can use the below query to format the date as Short Date.

SELECT FORMAT(GETDATE(), 'd', 'en-US') AS 'Short Date';

After executing the above query, I got the expected output as shown in the screenshot below.

sql date formatting functions

Example 2

We can use the following query to format the date as a long date.

SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy') AS 'Long Date';

As shown in the screenshot below, I obtained the expected output after executing the query above.

sql date formatting examples

Example 3

We can use the following query to format the date as a date and time.

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy hh:mm tt') AS 'Date and Time';

We obtained the expected output, as shown in the screenshot below, after executing the query above.

date formatting in sql

The FORMAT function makes date formatting much simpler, as you can directly specify the format you want without needing to use the style codes.

Approach 3: Combining DATEPART Functions

For more complex formatting requirements, you can combine multiple DATEPART functions with string concatenation.

Example:

SELECT 
    DATENAME(MONTH, GETDATE()) + ' ' + 
    CAST(DAY(GETDATE()) AS VARCHAR) + ', ' + 
    CAST(YEAR(GETDATE()) AS VARCHAR) AS 'Custom Date Format';

After executing the above query, I got the expected output as shown in the screenshot below.

date formatting in sql server

Best Practices

Below are the best practices to keep in mind.

1. Be Consistent

Select a standard date format for your application and adhere to it consistently. Consistency helps prevent confusion and errors.

2. Use Parameters for User Input

When accepting date inputs from users, always use parameterized queries to avoid SQL injection and ensure proper date parsing:

DECLARE @UserDate DATETIME = '2025-06-10';
SELECT FORMAT(@UserDate, 'MM/dd/yyyy', 'en-US') AS 'Formatted Date';

3. Store Dates in Standard Format

Always store dates in SQL Server’s native date or datetime types, not as strings. Convert data to specific formats only when displaying it to users.

5. Account for Time Zones

For applications serving users across time zones, consider using the datetimeoffset data type and UTC internally.

Common Date Formatting Scenarios

Below is a common scenario.

Scenario 1: Reporting with Different Date Formats

For reports, you often need to show dates in multiple formats:

SELECT 
    OrderDate,
    FORMAT(OrderDate, 'MM/dd/yyyy') AS 'US Format',
    FORMAT(OrderDate, 'yyyy-MM-dd') AS 'ISO Format',
    FORMAT(OrderDate, 'MMMM d, yyyy') AS 'Written Format'
FROM Orders;

After executing the above query, I obtained the expected output, as shown in the screenshot below.

date formatting sql server

Scenario 2: Grouping by Month and Year

When creating summary reports by month:

SELECT 
    FORMAT(Order_Date, 'MMMM yyyy') AS 'Month Year',
    COUNT(*) AS 'Order Count'
FROM Orders
GROUP BY FORMAT(Order_Date, 'MMMM yyyy')
ORDER BY MIN(Order_Date);

We obtained the expected output, as shown in the screenshot below, after executing the query above.

date formatting sql

Conclusion

Knowing the SQL Server date formatting is essential for any database developer. The methods I’ve shared—CONVERT, FORMAT, and custom DATEPART functions for handling any date formatting requirement.

The CONVERT function offers improved performance and compatibility with older versions, while the FORMAT function provides an intuitive syntax and greater flexibility.

You may also like the following articles.