[SOLVED] how to define date format in Azure synapse on demand

how to define date format in Azure synapse on demand

I am loading a couple of csv files using this Query

SELECT
    *
FROM
    OPENROWSET(
        BULK 'https://xxxxxx.core.windows.net/jde/*.CSV',
        FORMAT = 'CSV',
        FIELDTERMINATOR =',',
        FIRSTROW = 2 ,
        PARSER_VERSION='2.0'
    )
    with (
        Project   varchar(255),

"Description 2"   varchar(255),
"Unit Num"   varchar(255),
"Date Issue"   Date
) as rows

I get an error, my date format is 25/12/20, when I change the date for varchar everything works but obviously with the date is loaded as a text, how to define date format for synapse on demand

Solution

I was not able to parse the date correctly in the WITH statement. However, using CONVERT will convert the character date into DATE format.

SELECT
CONVERT(DATE, DateIssue, 3) as FormatDate,
*
FROM
    OPENROWSET(
        BULK 'https://storage.dfs.core.windows.net/datalakehouse/bronze/sourcedata/static/csvtest/test_ddmmyy.csv',
        FORMAT = 'CSV',
        PARSER_VERSION='2.0',
        FIRSTROW = 2
    ) 
WITH (
        DescriptionText   VARCHAR(10),
        UnitNum   TINYINT,
        DateIssue  VARCHAR(10)
    ) AS rowsoutput