CODEWITHSUNDEEP
azure-data-factoryazure-synapse
Sathya
Sathya

Reputation: 3

Azure synapse pipeline date format issue

I am currently using Azure synapse pipeline with Copy Activity which connects to the source ADLS which has CSV formatted file and in Sink to another directory converting to Parquet format.

In the CSV formatted files, there is a date Column with example 01/08/2023 and after converting to Parquet and when I create an external table on top of it the date column changes to 08-01-2023.

I tried using the mapping option in Copy Activity and for the column date I tried using datetime and datetimeoffset datatype but it didn't properly work.

I am still confused why the parquet format understands it in a different way. Is there a solution to keep the date format as YYY-MM-dd format in parquet file or in External table.

Source CSV

Target Parquet

The position of month and date changes.

Upvotes: 0

Views: 625

Answers (2)

lcolbert
lcolbert

Reputation: 11

Is your external table or the copy process specifying the date column as type date? Have you tried using type varchar(10)

Upvotes: 0

Pratik Lad
Pratik Lad

Reputation: 8301

Is there a solution to keep the date format as YYY-MM-dd format in parquet file or in External table.

When you read the data from the parquet file you need to set the column data type and for date data al the datatype are in yyyy-dd-MM format also we cand convert it while creating the table.

The workaround can be when you will execute select statement on the table at time convert it into your desired format by using below query:

select date as CONVERT(VARCHAR, Date, 101) from table

or you can create a view based on that table and convert the date into your desired format :

CREATE  VIEW [dbo].[View1]
AS  SELECT ID,PATIENTNAME ,
CONVERT(VARCHAR, Date, 101) as Datedd FROM table

select * from view1

Output:

enter image description here

Upvotes: 0

Related Questions