Sql Server Date Format Dd Mm Yyyy

5 min read

Working with dates in SQL Server often feels straightforward until the requirement arrives to display data in a specific regional format, such as dd mm yyyy. In practice, while the database engine stores datetime and date data types in an internal binary format—completely independent of human-readable separators—the presentation layer demands explicit conversion. Mastering this conversion is essential for developers, DBAs, and analysts who need to generate reports, export files, or build APIs that consume date strings in a day-month-year sequence.

This guide explores every practical method to achieve the SQL Server date format dd mm yyyy output, covering modern functions, legacy techniques, performance considerations, and common pitfalls to avoid.

Understanding SQL Server Date Storage vs. Display

Before diving into syntax, it is critical to internalize a fundamental concept: SQL Server does not store dates in a specific format.

When you define a column as DATE, DATETIME, DATETIME2, or SMALLDATETIME, the engine stores an integer representation (days since a base date) and, for time components, ticks since midnight. The format dd mm yyyy only exists when you convert that binary value to a string (VARCHAR or NVARCHAR) for output.

Attempting to "save" a date in dd mm yyyy format inside a date column is a category error. You save the date; you format it upon retrieval Worth keeping that in mind..

The Modern Standard: FORMAT() Function

Introduced in SQL Server 2012, the FORMAT() function is the most readable and flexible way to generate a dd mm yyyy string. It leverages the .NET Framework formatting engine, allowing standard and custom format strings Nothing fancy..

Basic Syntax

SELECT FORMAT(GETDATE(), 'dd MM yyyy') AS FormattedDate;
-- Output example: 25 12 2023

Key Format Specifiers

Pay close attention to the case sensitivity of the format string:

  • dd: Day of the month, 01 through 31 (zero-padded).
  • d: Day of the month, 1 through 31 (no leading zero).
  • MM: Month, 01 through 12 (Must be uppercase). Lowercase mm represents minutes.
  • yyyy: Year as a four-digit number.
  • yy: Year as a two-digit number.

Using Custom Separators

The requirement "dd mm yyyy" implies spaces, but real-world needs often require dashes, slashes, or dots. Because FORMAT treats characters literally unless they are specifiers, you can inject separators directly:

-- With dashes
SELECT FORMAT(GETDATE(), 'dd-MM-yyyy'); -- 25-12-2023

-- With slashes
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy'); -- 25/12/2023

-- With dots
SELECT FORMAT(GETDATE(), 'dd.MM.yyyy'); -- 25.12.2023

-- With spaces (as requested)
SELECT FORMAT(GETDATE(), 'dd MM yyyy'); -- 25 12 2023

Culture Awareness

One of FORMAT()'s superpowers is the optional culture argument. This ensures month names (if used) appear in the correct language, though for numeric dd MM yyyy the impact is minimal. On the flip side, it is best practice for global applications:

-- German culture (de-DE)
SELECT FORMAT(GETDATE(), 'dd MM yyyy', 'de-DE'); 
-- US English (en-US)
SELECT FORMAT(GETDATE(), 'dd MM yyyy', 'en-US');

Performance Caveat

Warning: FORMAT() is significantly slower than CONVERT() or CAST() because it invokes the CLR (Common Language Runtime). On small result sets (hundreds of rows), the difference is negligible. On large datasets (millions of rows), FORMAT() can become a bottleneck. For high-performance ETL or reporting queries, prefer the CONVERT method detailed below.

The High-Performance Classic: CONVERT() with Style Codes

Before FORMAT(), CONVERT() was the workhorse. It remains the fastest way to format dates in T-SQL because it operates natively within the SQL Server engine without CLR overhead The details matter here..

The Magic Style Code: 103

SQL Server defines specific "style codes" for CONVERT. Style 103 corresponds to the British/French standard: dd/mm/yyyy.

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS FormattedDate;
-- Output: 25/12/2023

Replacing Separators for "dd mm yyyy"

Style 103 hardcodes the forward slash (/). To achieve spaces (or dashes), you must wrap the conversion in a REPLACE() function Small thing, real impact..

-- For spaces: dd mm yyyy
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', ' ') AS FormattedDate;
-- Output: 25 12 2023

-- For dashes: dd-mm-yyyy
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '-') AS FormattedDate;
-- Output: 25-12-2023

Why VARCHAR(10)?

Always specify the length in VARCHAR(n). CONVERT with style 103 returns exactly 10 characters (dd/mm/yyyy). Declaring VARCHAR(10) avoids unnecessary memory grants and implicit conversion warnings in execution plans.

Other Relevant Style Codes

Style Code Standard Output Pattern Separator
103 British/French dd/mm/yyyy /
104 German dd.mm.yyyy `.

If your target format is dd-mm-yyyy, Style 105 is natively perfect and requires no REPLACE():

-- Native dd-mm-yyyy (Style 105)
SELECT CONVERT(VARCHAR(10), GETDATE(), 105); 
-- Output: 25-12-2023

Building Strings Manually: DATEPART and CONCAT (SQL Server 2012+)

For absolute control without relying on style codes—or if you need to construct a string in a non-standard order—you can assemble the parts using DATEPART (or DAY, MONTH, YEAR) and CONCAT (or FORMATMESSAGE/STRING_AGG in newer versions) And that's really what it comes down to..

Using CONCAT and FORMAT (for zero-padding)

DATEPART returns integers (e.In practice, g. , 1 for January). You must pad them to two digits.

SELECT CONCAT(
    FORMAT(DAY(GETDATE()), '00'), ' ',
    FORMAT(MONTH(GETDATE()), '00'), ' ',
    YEAR(GETDATE())
) AS FormattedDate;
-- Output: 25 12 2023

Using RIGHT and String Concatenation (Pre-201

2 versions and older, the RIGHT function becomes essential. Because DAY() and MONTH() return integers, you must left-pad them to two digits before concatenation. The RIGHT function can help, though a simple CAST and string-building pattern often works just as well:

SELECT 
    RIGHT('0'
Just Went Online

New on the Blog

See Where It Goes

Also Worth Your Time

Thank you for reading about Sql Server Date Format Dd Mm Yyyy. We hope the information has been useful. Feel free to contact us if you have any questions. See you next time — don't forget to bookmark!
⌂ Back to Home