5

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.

1

3 Answers 3

14

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
)
0
5

I agree that there is no direct conversion, but this is pretty simple, however performance will be bad, so stick with a bit of string manipulation!

    DECLARE @datevar DATE
    SET @datevar = TRY_PARSE(FORMAT(TRY_CAST('31012016' AS INT),'00/00/0000')
                   AS DATE USING 'PT-BR')
    SELECT @datevar;
2

No, I could not find any way to get it to implicitly convert. This is the simplest I could come up with.

DECLARE @etldate varchar(max) = '31012016';

DECLARE @datevar date = CAST(SUBSTRING(@etldate, 3, 2) + '/' + SUBSTRING(@etldate, 1, 2) + '/' + SUBSTRING(@etldate, 5, 4) AS date);  
SELECT @datevar; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.