Calculate ISO year in DQM mode

[Abagnale]

Hello,
I am working on a migration project to migrate some frameworks from oracle to redshift, in order to connect to redshift I have switched the frameworks from CQM to DQM.
to calculate the iso week (IW) and iso year (IYYY) in oracle, this formula was used: to_char({sysdate}, 'IYYYIW')
I found _week_of_year() a function that can get me the week part but I'm still stuck on the year part

My question is: knowing that the to_char function is not supported in DQM, how can I calculate the iso year in cognos?

[MFGF]

Hello,
I am working on a migration project to migrate some frameworks from oracle to redshift, in order to connect to redshift I have switched the frameworks from CQM to DQM.
to calculate the week and iso year in oracle, this formula was used: to_char({sysdate}, 'IYYYIW')
I found _week_of_year() a function that can get me the week part but I'm still stuck on the year part

My question is: knowing that the to_char function is not supported in DQM, how can I calculate the iso year in cognos?

Have you looked at the extract() function? You can use it to extract the year, month and day parts from a date.

Cheers!

MF.

[Abagnale]

Yes I Have

The extract function extract(year, current_date) gives  you the gregorian year but what i need to calculate is ISO year

This is the definition o ISO Year in wikipedia : "An ISO week-numbering year (also called ISO year informally) has 52 or 53 full weeks. That is 364 or 371 days instead of the usual 365 or 366 days. The extra week is sometimes referred to as a leap week, although ISO 8601 does not use this term.

Weeks start with Monday. Each week's year is the Gregorian year in which the Thursday falls. The first week of the year, hence, always contains 4 January. ISO week year numbering therefore slightly deviates from the Gregorian for some days close to 1 January." https://en.wikipedia.org/wiki/ISO_week_date

the ISO year of the date 02 january 2021 would be 2020 and not 2021 (wich is the gregorian year)

[dougp]

In Oracle, isn't...

Code Select Expand

to_char({sysdate}, 'IYYYIW')
...equivalent to...

Code Select Expand

to_char({sysdate}, 'IYYY') || to_char({sysdate}, 'IW')
...?

It looks like Amazon Redshift (https://docs.aws.amazon.com/redshift/latest/dg/r_FORMAT_strings.html) uses the same core formatting codes, but may not allow you to combine them conveniently like Oracle does and doesn't pad the value with zeros.  So...

Code Select Expand

to_char(to_number(to_char({sysdate}, 'IYYY'), '9999'), '0000') || to_char(to_number(to_char({sysdate}, 'IW'), '99'), '00)
...or...

Code Select Expand

lpad(to_char({sysdate}, 'IYYY'), 4, '0') || lpad(to_char({sysdate}, 'IW'), 2, '0')

[Abagnale]

That would work in redshift, but not in Framework Manager because my project's query mode is DQM (I switched from CQM to DQM to be able to use RedShift)

In DQM mode, FM does not support vendor functions (RedShift) and you need to use only cognos functions
Every time I use the to_char() function I get the following message : "error: XQE-PLN-0098 The vendor specific function "to_char" is not supported."

So I have to find a way to calculate the equivalent of to_char ({sysdate}, 'IYYY') using only cognos functions

[dougp]

From the Information box in the Cognos Data Item Expression dialog:

_week_of_year ( date_expression )
Returns the number of the week of the year of "date_expression" according to the ISO 8601 standard.

So, if you don't need to worry about dates before 1000-01-01:

Code Select Expand


cast(
if (_week_of_year([Date]) = 1)
then (_year(_add_days([Date], 7)))
else (
if (_week_of_year([Date]) > 51)
then (_year(_add_days([Date], -14)))
else (_year([Date]))
)
, varchar(4))
||
substring('0' || cast(_week_of_year([Date]), varchar(2)), char_length(_week_of_year([Date])), 2)