question

SrikanthThota-458 avatar image
0 Votes"
SrikanthThota-458 asked ShaikMaheer-MSFT commented

Convert timestamp to integer in ADF dataflow

Hello All,

I need to convert timestamp(2014-06-19 00:00:00.000) to Integer(yyyyMMdd) in ADF dataflow. I have used derived column and in the expression i wrote this below

toInteger(toString(toDate('2014-06-19 00:00:00.000', 'yyyy-MM-dd HH:mm:ss'), 'yyyyMMdd'))

and its working perfectly am able to convert to integer required format but the data is taking default value which i kept in the expression i.e..,2014-06-19 00:00:00.000

when i tried to replace with column name like below :

toInteger(toString(toDate('modified', 'yyyy-MM-dd HH:mm:ss'), 'yyyyMMdd')) , am receiving null values.

Could someone help me here?

azure-data-factoryazure-synapse-analytics
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @SrikanthThota-458 ,

Just checking in to see if the below answer helped. If this answers your query, do click 130616-image.png and upvote 130671-image.png for the same. And, if you have any further query do let us know.

0 Votes 0 ·

1 Answer

ShaikMaheer-MSFT avatar image
0 Votes"
ShaikMaheer-MSFT answered ShaikMaheer-MSFT edited

Hi @SrikanthThota-458,

Thank you for posting query in Microsoft Q&A Platform.

As per my understanding you are trying to take a column which contains dates and want to generate integer from it in format yyyyMMdd. Please correct me if I am wrong.

I see that you mentioned your column name in single quotes. If you do that it comes string not a column. So you should consider removing single quotes there.

toInteger(toString(toDate('modified', 'yyyy-MM-dd HH:mm:ss'), 'yyyyMMdd')) your expression. If you observe here column name modified is inside single quotes.

Please check below screenshots for better idea.

191190-image.png

Hope this helps. Please let us know if any further queries.


Please consider hitting Accept Answer. Accepted answers help community as well.


image.png (152.7 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you for responding @ShaikMaheer-MSFT :

I even tried with & without single quote but when i remove single quote the expression throws an error ,please refer pic.191331-date.png




But i managed to solve this by using this : toInteger(toString(toDate({modified}), 'yyyyMMdd'))

however without single quotes why it is working in your case? can you explain please

0 Votes 0 ·
date.png (4.4 KiB)

Hi @SrikanthThota-458 , Glad to know your issue resolved.

Ques: without single quotes why it is working in your case? can you explain please
When we use column names we should not include them in single quotes. The moment we enclose them with single or double quotes, data flow treat it as some string value. Data flow will not treat it as column.


Please consider hitting Accept Answer button. Accepted answers help community as well.

0 Votes 0 ·