Date problem - add year in formatDate

Highlighted
Six Stars

Date problem - add year in formatDate

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?


Accepted Solutions
Eight Stars

Re: Date problem - add year in formatDate

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,

View solution in original post


All Replies
Eight Stars

Re: Date problem - add year in formatDate

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,

Eight Stars

Re: Date problem - add year in formatDate

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,

View solution in original post

Employee

Re: Date problem - add year in formatDate

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.

image.png

 

image.png

 

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

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog