Convert DDMMYYYY to DATE in SQL Server

by

Sometimes you might get dates in a non-standard format, such as DDMMYYYY. By “non-standard” I mean a format that SQL Server doesn’t recognize as a date. Such a format is actually a little ambiguous. Some dates presented in this format could be mistaken for another date. For example, 02082026 could be August 2, 2026, or it could be February 8, 2026 depending on which locale you’re using.

Therefore, we need to do a bit of work in order to get SQL Server to recognize it as a date. Once that’s done, it’s a simple matter of converting it to an actual DATE type.

Below are a few options for converting a DDMMYYYY string to a DATE in SQL Server.

Option 1: DATEFROMPARTS() with SUBSTRING()

One option for converting DDMMYYYY to a DATE in SQL Server is by combining the DATEFROMPARTS() function with the SUBSTRING() function:

DECLARE @DateString VARCHAR(8);
SET @DateString = '02082025';

SELECT DATEFROMPARTS(
  SUBSTRING(@DateString, 5, 4), -- Year
  SUBSTRING(@DateString, 3, 2), -- Month
  SUBSTRING(@DateString, 1, 2)  -- Day
);

Output:

2025-08-02

Option 2: TRY_CONVERT with STUFF()

Another option is to use TRY_CONVERT() along with some string manipulation. In this case, we’ll use STUFF() to manipulate the string:

SELECT TRY_CONVERT(DATE, STUFF(STUFF('02082025', 5, 0, '/'), 3, 0, '/'), 103);

Result:

2025-08-02

Here’s a breakdown of each stage:

  • STUFF('02082025', 5, 0, '/'): This inserts a / at the 5th position (after MM). The string becomes '0208/2025'.
  • STUFF(..., 3, 0, '/'): This inserts another / at the 3rd position (after DD). The string becomes '02/08/2025'.
  • TRY_CONVERT(DATE, ..., 103): The final string is now in a format that the TRY_CONVERT() function with style 103 can reliably parse.

TRY_CONVERT() can be useful for ETL processes or working with user data, as it prevents query failures on bad data.

Option 3. CONVERT() with STUFF()

This option is almost identical to the previous one. The only difference is that we’re using CONVERT() instead of TRY_CONVERT(). These functions work the same, except that CONVERT() will produce an error if the conversion fails. This approach could be more suitable if you don’t need the error-handling of TRY_CONVERT(), or if you’re not on SQL Server 2012+.

SELECT CONVERT(DATE, STUFF(STUFF('02082025', 5, 0, '/'), 3, 0, '/'), 103);

Result:

2025-08-02

Option 4. CONVERT() with SUBSTRING() and Standard Format

This method is also quite common and effective. It builds the date string in the ISO standard YYYY-MM-DD format, before converting it to an actual DATE type:

SELECT CONVERT(DATE,
  SUBSTRING('02082025', 5, 4) + '-' +
  SUBSTRING('02082025', 3, 2) + '-' +
  SUBSTRING('02082025', 1, 2));

Output:

2025-08-02

Like the previous example, this will throw an error on invalid dates (given we’re using CONVERT() instead of TRY_CONVERT()). But you can always replace CONVERT() with TRY_CONVERT() if required.