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.