topic Date problem - add year in formatDate in Talend Studio

Date problem - add year in formatDate

Gabri11 — Sat, 16 Nov 2024 03:42:19 GMT

hi everyone, I have a problem with a date.
i have a date variable taken from a database field for use in a query in another database.
If I print the variable I see "Mon Dec 30 12:33:35 CET 2019" but I have to transform it into the type "dd / MM / YYYY" then I transform it through "TalendDate.formatDate (" dd / MM / YYYY ", context.LastDate) .
The problem that adds a year to the date displaying "12/30/2020" and not "12/30/2019".
If the variable is valued at 29/12/2019 until 23:59 all works correctly, while from 30/12 Talend adds one year.
Can someone help me? is it a bug?

Re: Date problem - add year in formatDate

rmartin2 — Mon, 30 Dec 2019 18:34:56 GMT

Hi,

Not sure to understand all you are saying, but basically :

A "Date" in Talend, like in nearly any DBMS is associated with a timestamp precise to the millisecond
You can select a "preferred format" for displaying it in the column dedicated
To properly print it as you wish, you need to use TalendDate.formatDate

Going with that, just take your date and put these components :

Input =>tMap=>tLogRow in Table mode

The input is you date.

In the tMap, create 2 columns :

The date with a specified format
A string formatting your date

If you can show us your tMap and the result we could help you.

Sincerely,

Re: Date problem - add year in formatDate

rmartin2 — Thu, 02 Jan 2020 16:15:03 GMT

Hi @gabrig11 ,

I figured it out while debugging one of colleague job !

Short version :
Never use "YYYY" but "yyyy". "YYYY" is ISO year, and "yyyy" is calendar year. In database it's often written "IYYY".

Long version :

When using weeks, you have to decide to which year it belong. For this year, we had Monday and Tuesday in 2019 and the other day in 2020. In ISO norm, the week belong to the year owning the Thursday (on simpler, the one that has the greatest number of days of this week).

So technically, in ISO standard, 30th December 2019 is 1st day of 1st week of 2020. So the ISO year is 2020, not 2019.

Same for 31th.

Long story short : unless you are querying with weeks, you should never use "YYYY". Always use "yyyy".

Sincerely,

Re: Date problem - add year in formatDate

Anonymous — Thu, 02 Jan 2020 16:25:59 GMT

Hi,

I got the same error when you are trying the implicit conversion where I am trying to parse the input value directly to output column in target dd/MM/yyyy format. I believe the time zone value of +1 is getting added to year. I would recommend to create a JIRA ticket for the same using below link.

https://jira.talendforge.org/

A quick work around will be as shown below where we are allowing the date value to be stored to an intermediate layer and its giving correct result.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved