CODEWITHSUNDEEP
sqlhana
hana_wannabe
hana_wannabe

Reputation: 45

SAP HANA convert YYYYWW to MM/DD/YYYY

The requirement is to get the Monday and Sunday from the field SPWOC in table S990. I cant think of any function combinations that would allow me to pull this information. An example output from that table-field is 202135. So I'm trying to get the Monday and Sunday from the 35th week of 2021. Any ideas on where to start would be greatly appreciated!

Upvotes: 1

Views: 1347

Answers (1)

Lars Br.
Lars Br.

Reputation: 10396

Whether the ISO week calculation (that's the SAP standard default) is used or the system uses a non-ISO week calculation method cannot be determined from the table alone.

To find out about that, you need to talk to somebody that knows how the SAP system is configured.

But for the scope of the question, let's assume the system you work with uses the ISO method, where weeks start on Monday and end on Sunday.

Even with that, it's not straightforward to compute the dates for Monday and Sunday of the Xth week in a closed-form. The TO_DATE() function does not support a direct conversion from YYYYWW to YYYY-MM-DD either.

So, apart from writing your own function to perform the right calendar arithmetic, I only see the following option as a feasible approach:

  1. Ensure the HANA table _SYS_BIC.M_TIME_DIMENSION has been loaded with data (day-level) for the time frame you are interested in. This can be done via the Generate Time Data function in HANA Studio or the Web IDE. Check the documentation for details on that if required.

  2. With the time dimension table you get all sorts of precomputed date representations and conversion, including CALWEEK and DAY_OF_WEEK.

With that you can run a SELECT like this:

select 
     year
   , week
   , date_sql
   , to_nvarchar( date_sql, 'DAY')
from 
    "_SYS_BI"."M_TIME_DIMENSION" 
where 
    calweek = '202135'
and day_of_week in (0, 6);


YEAR    WEEK    DATE_SQL    TO_NVARCHAR(DATE_SQL,'DAY')
2021    35      30/08/2021  MONDAY                     
2021    35      05/09/2021  SUNDAY

Upvotes: 1

Related Questions