Date problem - add year in formatDate

[Gabri11]

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?

[rmartin2]

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 :

1. The date with a specified format
2. A string formatting your date

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

Sincerely,

[rmartin2 · Accepted solution]

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,

[Anonymous]

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