In this comprehensive article, I’ll share approaches to SQL Server date formatting with style code 101, covering everything from basic conversions to advanced techniques that’ll help you handle any date formatting scenario.
SQL Server Date Format 101
Before diving into the technical details, let’s understand why it is crucial:
- Data consistency: Standardized date formats ensure consistent interpretation across applications
- Localization requirements: Different regions expect different date formats (MM/DD/YYYY in the US vs. DD/MM/YYYY in Europe)
- Reporting accuracy: Improper date handling can lead to incorrect reporting results
- User experience: Presenting dates in familiar formats improves application usability
Now, let’s explore the various methods for formatting dates with style code 101 in SQL Server.
Approach 1: Using the CONVERT Function for Date Formatting
The CONVERT Function is the traditional workhorse for date formatting in SQL Server.
Syntax
The basic syntax is:
CONVERT(varchar, date_value, format_code)Where format_code is a numeric value that specifies the desired output format.
Here’s a table of the most common format codes I use daily:
| Format Code | Description | Example Output |
|---|---|---|
| 101 | US date format (MM/DD/YYYY) | 06/15/2025 |
| 103 | British/French date (DD/MM/YYYY) | 15/06/2025 |
| 110 | USA date (MM-DD-YYYY) | 06-15-2025 |
| 112 | ISO date (YYYYMMDD) | 20250615 |
| 120 | ODBC canonical (YYYY-MM-DD HH:MI) | 2025-06-15 14:30:15 |
| 126 | ISO8601 (YYYY-MM-DDTHH:MI.mmm) | 2025-06-15T14:30:15.123 |
Example
The query below will help convert to a basic US date format (MM/DD/YYYY).
-- Basic US date format (101)
SELECT CONVERT(varchar, GETDATE(), 101) AS [US_Date_Format]After executing the above query, I obtained the expected output, as shown in the screenshot below.
When to Use CONVERT for Date Formatting
I recommend using the CONVERT function when:
- Working with SQL Server 2008 or older versions
- Needing maximum compatibility across different SQL Server versions
- Requiring specific regional date formats identified by standard format codes
- Implementing date logic in stored procedures that might be called from various applications
Approach 2: FORMAT Function for More Flexible Date Formatting
For SQL Server 2012 and newer, I often prefer the FORMAT A function that offers more intuitive and flexible date formatting options. This function adheres to the .NET formatting syntax, making it particularly convenient for developers with a C# background.
Syntax
The basic syntax is:
FORMAT(date_value, format_string [, culture])Example
We can use the query below to convert the basic US date format, such as the 101 style code.
-- Basic US date format
SELECT FORMAT(GETDATE(), 'MM/dd/yyyy') AS [US_Date]After executing the above query, I obtained the expected output, as shown in the screenshot below.
Custom Format Specifiers
When working with the FORMAT Function, I rely on these format specifiers:
- d: Day of month (1-31)
- dd: Day of month with leading zero (01-31)
- ddd: Abbreviated day name (Mon, Tue, etc.)
- dddd: Full day name (Monday, Tuesday, etc.)
- M: Month (1-12)
- MM: Month with leading zero (01-12)
- MMM: Abbreviated month name (Jan, Feb, etc.)
- MMMM: Full month name (January, February, etc.)
- yy: Year, two digits (25)
- yyyy: Year, four digits (2025)
- h: Hour 12-hour format (1-12)
- hh: Hour 12-hour format with leading zero (01-12)
- H: Hour 24-hour format (0-23)
- HH: Hour 24-hour format with leading zero (00-23)
- m: Minutes (0-59)
- mm: Minutes with leading zero (00-59)
- tt: AM/PM designator
When to Use the FORMAT Function
I recommend using the FORMAT function when:
- Working with SQL Server 2012 or newer versions
- Needing highly customized date formats beyond the standard CONVERT codes
- Requiring specific cultural formats using the culture parameter
- Creating user-facing outputs where presentation is important
Conclusion
This article explains how to format dates using style code 101, providing multiple examples. I have also explained when to use which approach or method. I hope this information will help you meet this specific requirement.
You may also like the following articles.
- SQL Server Add 1 Day To Date
- How To Convert Date To String In SQL Server
- SQL Server Date Data Types
- SQL Server Date Formatting
After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.