Sql-server – Force SQL Server to parse “DDMMYYYY”

date formatsql serversql-server-2012

I'm developing ETL from a file where dates are expressed in the format "DDMMYYYY", example "31012016".

I'd like to grab this string and pass it directly to the SQL text where data will be inserted. I don't want to parse it into C# DateTime to then format it to "YYYY-MM-DD" so that SQL Server parses it once more.

As I do currently, the date string is parsed once by .Net, then formated by .Net and printed in the SQL code, then parsed again by SQL Server. I want to grab the string from the flat file and place it directly into SQL code without any conversion.

SQL Server recognizes "YYYYMMDD", but it fails for "DDMMYYYY" with message:

Conversion failed when converting date and/or time from character string.

Example:

DECLARE @datevar date = PARSE('31012016' as date using 'pt-BR');  
SELECT @datevar;

Is there any way to make it work?

I tried PARSE('31012016' as date using 'pt-BR') but raised exception. TRY_PARSE returns null. Also tried CAST and CONVERT (Transact-SQL) but there doesn't seem to exist this style.

Best Answer

When a date is represented as 8 numerals without any delimiters, SQL Server will always try to interpret it as YYYYMMDD. That format has deliberately been chosen as locale- or culture-independent (being also one of ISO 8601 standard date formats, as a matter of fact), and there is no way to have the server interpret it as DDMMYYYY or in any other way.

So, either rearrange the date parts of your DDMMYYYY string to make it YYYYMMDD:

CONVERT(
  date,
  SUBSTRING(@datestring, 5, 4) + SUBSTRING(@datestring, 3, 2) + SUBSTRING(@datestring, 1, 2)
)

or insert delimiters to make it match one of the known delimited DMY formats, for instance DD/MM/YYYY:

CONVERT(
  date,
  STUFF(STUFF(@datestring, 5, 0, '/'), 3, 0, '/'),
  103  -- DD/MM/YYYY
)
Related Question