Datetime with NULL values

Alyssa Bleize 0

Hello,

I have two datetime columns in this format: "20180426173754".
it is from a JSON file and ADF says that is it a timestamp datatype.

But in my JSON file I can see that one column has a datetime datatype and one value has a timestamp datatype.

But I can't see any of my datetime values, everything gives a NULL value in ADF.

Can somebody please help me, because I tried to convert the datatypes to string, date, datetime but nothing works.

Michael Liben 171 Reputation points

2023-03-22T10:11:06.32+00:00

That format appears to be a fixed length field type where the first four characters are the four digit year followed by a two-digit month, two-digit day, two-digit hour, two-digit minute, and two-digit second. That would equate to Apr 26, 2018 at 17:37:54. Since the hour appears be 24-hour format, no AM or PM is required. Not shown here is the offset from GMT so probably safe to assume it is local time at the source server.

I'm not familiar with ADF but you might want to try modifying the JSON by replacing the date string into a format that is identifiable as a date by ADF.
KranthiPakala-MSFT 46,462 Reputation points Microsoft Employee

2023-03-27T22:36:16.0733333+00:00

@Alyssa Bleize Just checking in to see if the below information was helpful. If it answers your query, please do click Accept Answer and Yes for "was this answer helpful", as it might be beneficial to other community members reading this thread. If you have any further query, do let us know.

Thank you

1 answer

Amira Bedhiafi 23,011 Reputation points

2023-03-22T12:14:57.7866667+00:00

It's possible that the timestamp format in your JSON file is not recognized by ADF.

Try the following :

SELECT FROM_UNIXTIME(timestamp_column) AS datetime_column FROM table_name

If the datetime values still cannot be read by ADF, try casting them to a different format using the DATE_FORMAT:

SELECT DATE_FORMAT(datetime_column, 'YYYY-MM-DD HH:MM:SS') AS formatted_datetime_column FROM table_name
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Your answer