Convert int to datetime in ADF

Question

Convert int to datetime in ADF

Venkatesh Srinivasan 86

Hi @ShaikMaheer-MSFT @HimanshuSinha-msft

I would like to convert my INT datatype column to datetime.

Example :

I can get the date out of my column. Can you please help me how to get the timestamp along with the date.

Thanks in advance!

Accepted answer

0 additional answers

Your answer

Answer 1

Michael Taylor 60,161

FileNameDate is a text column, not an integer. That makes sense because an regular 32-bit int cannot hold a full date value (YY,YYM,MDD). Hence this is just a string parsing problem.

Unfortunately your time isn't in a consistent format so there isn't going to be a "correct" way to do this. Given the following value: 12345, is this 12:34:05, 1:23:45 or something else? Unless you have the full 6 digit timestamp you are going to have to make some decisions on how to interpret the value.

Given just your first value 21102531021 if you assume the first 6 digits are the date then this results in 10/25/2021. That leaves the remaining time of 311021 which is not a valid hhmmss value. Is this a Unix timestamp or something that represents ticks?

Venkatesh Srinivasan 86 Reputation points

2021-11-16T16:06:45.827+00:00

Hi, Sorry for the confusion here. actually row 2 and 4 are my actual format. you can consider 2 & 4th row of preview section
Michael Taylor 60,161 Reputation points

2021-11-16T16:14:57.26+00:00
So 211110110210 would be Nov 11, 2021 at 11:02:10 AM. toDate only does dates. To do a date and time then switch to toTimestamp. Perhaps something like this:

toTimestamp(FileNameDate, 'yyMMddkkmmss')

Note that hh indicates a 12 hour time. In the example I'm using kk which indicates 24 hour time so 14 would be treated as 2 PM.
Venkatesh Srinivasan 86 Reputation points

2021-11-16T16:18:30.033+00:00

Thanks a lot for your prompt response. Really helpful! :)

Share via

Convert int to datetime in ADF

0 additional answers

Your answer