CODEWITHSUNDEEP
azure-data-factory
user2181700
user2181700

Reputation: 177

Converting string to date , datetime or Int in Mapping dataflow

I have a parquet file with a start_date and end_date columns Formatted like this

01-Jan-2021

I've tried every combination conversion toDate, toString, toInterger functions but I still get Nulls returned when viewing the data (see image).

I would like to have see the result in two ways YYYYMMDD as integer column and YYYY-MM-DD as Date columns. eg 01012021 and 01-01-2021

I'm sure the default format has caused this issue.

enter image description here

Thanks

Upvotes: 0

Views: 5967

Answers (2)

Mark Kromer MSFT
Mark Kromer MSFT

Reputation: 3838

First, for the Date formatter, you need to first tell ADF what each part of your string represents. Use dd-MMM-yyy for your format. Then, use a string formatter to manipulate the output as such: toString(toDate('01-Jan-2021', 'dd-MMM-yyyy'), 'yyyy-MM-dd')

For the integer representation: toInteger(toString(toDate('01-Jan-2021', 'dd-MMM-yyyy'), 'yyyyMMdd'))

Upvotes: 3

KarthikBhyresh-MT
KarthikBhyresh-MT

Reputation: 5034

Ah, you say *"I would like to have see the result in two ways YYYYMMDD as integer column and YYYY-MM-DD as Date columns. eg 01012021 and 01-01-2021"* Do you want in YYYYMMDD or dd-mm-yyy cause your example is in the later format.

Anyways, please see below expression you could use:

My source:

enter image description here

Use derived column:

enter image description here

Edit expression:

start_date_toInteger : toString(toDate(substring(start_date,1,11), 'dd-MMM-yyyy'), 'yyyymmdd')

start_date_toDate: toString(toDate(substring(start_date,1,11), 'dd-MMM-yyyy'), 'yyyy-mm-dd')

enter image description here

Final results:

enter image description here

Upvotes: 2

Related Questions