I am trying to query Azure Storage Account CDM csv file from SQL server on-prem. I created external table with all the data type as varchar. However, I need to display date time as datetime and need to remove double quote from string while displaying result. What should be the file format should I choose for the same. Please refer screenshot with current field values.
The file format used is below.
CREATE EXTERNAL FILE FORMAT [TextFile]
(FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = N',', USE_TYPE_DEFAULT = False))
I am aware there is DATE_FORMAT option, however, I tried with few and it is failing with varchar to datetime conversion error.
Current records that need to modify with right format.