topic Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity in Pipelines

Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

pmscorca — Tue, 02 Jul 2024 20:46:52 GMT

Hi,

I'm implementing a data pipeline and I'm using a copy activity adding a custom datetime column to save in the destination the load data date.

For this column I'm using this expression:

@formatDateTime(utcNow(),'yyyy-MM-ddTHH:mm:ss')

that returns an undesidered AM/PM indicator.

Viewing the input information of the related Fabric data pipeline I can see this:

but in the Fabric lakehouse I can see this:

I'd like to show the time referring to the 24-hours clock.

Any helps to me, please? Thanks

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

Anonymous — Fri, 05 Jul 2024 01:05:05 GMT

Hi pmscorca

I think this may have something to do with the date/time format of utcnow().

You can try changing utcnow () to now ().

@formatDateTime(Now(),'yyyy-MM-ddTHH:mm:ss')

Let me know if you have any questions.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

pmscorca — Thu, 04 Jul 2024 13:53:23 GMT

Hi,

I obtain this error:

Also writing Now().

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

Anonymous — Fri, 05 Jul 2024 01:03:31 GMT

Hi pmscorca

In an expression, HH indicates the 24-hour format, not the 12-hour format.

If you still see AM/PM indicators, it may be due to the way data is interpreted or displayed in the Fabric lakehouse.

I suggest you check the following:

Data types: Make sure that the columns in the Fabric lakehouse are set to date-time types that support 24-hour formats.

Pipeline configuration: Carefully review the Settings of the replication activity to ensure that there are no conversions or Settings that change the date-time format.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

frithjof_v — Fri, 05 Jul 2024 06:56:15 GMT

pmscorca it seems your Lakehouse column is text type (ABC).

Do you want the Lakehouse column to be text type or datetime type?

If you actually want it to be text type, could you write the code like this?

'yyyy-MM-dd HH:mm:ss'

(Only change is to remove the T. Just to try if this changes something).

If the Lakehouse column was datetime type, maybe your local settings on the computer or web browser determines how the Lakehouse explorer displays the values.

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

pmscorca — Fri, 05 Jul 2024 13:26:14 GMT

Hi,

I'm trying to add a custom datetime column in a copy activity to add this data to the source data to copy in a lakehouse; so, it should be the copy activity to establish automatically the right data type for the destination datetime column.

It is correct to use 'HH' in uppercase to obtain a time that refers to a 24-hours clock; writing 'yyyy-MM-ddTHH:mm:ss' with or without the T letter is the same thing.

It seems that I've solved at least partially, perhaps for a Fabric update.

I think that the copy activity should establish the right data type for the columns of the destination lakehouse table, if it doesn't exist. The formatDateTime function write a varchar(8000) column into a lakehouse and not a datetime/timestamp column and this is an undesirable behaviour.

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

frithjof_v — Fri, 05 Jul 2024 15:12:44 GMT

Have you tried just using this formula:

@utcNow()

Perhaps this creates a date/time column in the Lakehouse table.

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

frithjof_v — Fri, 05 Jul 2024 15:47:31 GMT

I can make it work (get date or date/time data type) by doing like this:

Source:

Mapping:

It is now a date/time column in the Lakehouse table (but still displaying PM symbol).

In the SQL Analytics Endpoint it is not displaying AM/PM. There it is being displayed in 24 hour-format, and with higher precision (microsecond, i.e. six digits behind the second).

So I guess the AM/PM format is just the display format which is being used in the Lakehouse explorer. Unless anyone knows how to change that display format (maybe some kind of regional settings).

Example of Lakehouse Explorer:

Same data viewed in SQL Analytics Endpoint:

Re: Formatting a custom datetime colums referring to a 24-hours clock in a copy activity

pmscorca — Fri, 05 Jul 2024 16:47:00 GMT

Doing New mapping, the dynamic mapping would be lost.

New mapping means custom mapping.