IBM Mainframe Forum

by **ANGELDANY** » Tue Nov 16, 2021 4:56 pm

Hi,
I have Dates in string format YYYYMMDD, how can i get the week number?? The "WEEK_ISO" command does not work.

by **sergeyken** » Tue Nov 16, 2021 8:01 pm

ANGELDANY wrote:Hi,
I have Dates in string format YYYYMMDD, how can i get the week number?? The "WEEK_ISO" command does not work.

Provide your code here.
Provide your desired, and actual results produced.
Provide all error messages, if any.

Besides this: the WEEK_ISO is not a command; it is a function in DB2 environment.
Are you sure you are using DB2? From where: from COBOL, from REXX, from SPUFI, from Assembler, from Java

, or maybe from FORTRAN?

Do not forget to use the CODE tags for all your code samples.

by **ANGELDANY** » Tue Nov 16, 2021 8:14 pm

I reported some columns of an AS400 table on Excel,i would like to add a new column (week iso), the column with the dates is in format YYYYMMDD, i am writing the query on excel, but I get the error as in the picture.

by **prino** » Tue Nov 16, 2021 8:44 pm

ANGELDANY wrote:I reported some columns of an AS400 table on Excel,i would like to add a new column (week iso), the column with the dates is in format YYYYMMDD, i am writing the query on excel, but I get the error as in the picture.

And what does this have to do with mainframes???

by **ANGELDANY** » Tue Nov 16, 2021 8:51 pm

Sorry prino, as you will have understood I'm not an expert, can you still help me?? Can you suggest me the correct section?

by **sergeyken** » Tue Nov 16, 2021 11:48 pm

ANGELDANY wrote:I reported some columns of an AS400 table on Excel,i would like to add a new column (week iso), the column with the dates is in format YYYYMMDD, i am writing the query on excel, but I get the error as in the picture.

It is nice you asked your questions not at an auto-mechanics forum... :roll:

I repeat again, for the last time, in a larger font:
Provide your code here.
Provide your desired, and actual results produced.
Provide all error messages, if any.

I do not understand Spanish (or Portugal?) well, but even I can find from your error message: the type of (one of) parameters for WEEK_ISO is wrong!!!

That's why all the information listed above is mandatory.

If you prefer to ignore the advices given at this forum, then I do not understand: what is the goal of asking questions??

by **ANGELDANY** » Wed Nov 17, 2021 1:28 pm

Select all

SELECT DISTINCT PMORDF.PROFILO AS "INSERITO DA", 
PMORDF.TIPO AS "PRELEVATO", 
PMORDF.ORDPRO AS "ORDINE LAVORO", 
PMORDF.CODART AS "COD. ARTICOLO", 
(PMORDF.QTRDPO - PMORDF.QTNTPO) AS "QUANTITÀ RESIDUA",
YEAR (DTSCAD) AS "ANNO", (DAY (DTSCSD) - DAY (DTIPPO)) AS "GG ATTRAVERSAMENTO",
DATE(INSERT(INSERT(LEFT(CHAR(DTSCAD),8),5,0,'-'),8,0,'-')) AS "FINE PROD.",
CURRENT DATE AS "DATA CORRENTE",
(DATE(INSERT(INSERT(LEFT(CHAR(DTSCAD),8),5,0,'-'),8,0,'-'))) - (CURRENT DATE) AS "RITARDO",
PMORDF.TORDPO AS "STATO", 
PMORDF.STAMP AS "STAMPA", 
PMORDF.QTNTPO AS "QT. VERSATA", 
MGARTF.DSARMA AS "DESCR. ARTICOLO"
FROM S1000000.RAI01DAT.MGARTF MGARTF, S1000000.RAI01DAT.MGART1F MGART1F, S1000000.RAI01DAT.PCIMPF PCIMPF, S1000000.RAI01DAT.PMORDF PMORDF
WHERE PCIMPF.ORPRCI = PMORDF.ORDPRO
AND MGART1F.CDARMD = PCIMPF.CDARCI
AND MGARTF.CDARMA = MGART1F.CDARMD
AND ((PMORDF.DTINPO>20201231))
AND TIPO <>'S'
AND (QTRDPO-QTNTPO) >0
AND (DATE(INSERT(INSERT(LEFT(CHAR(DTSCAD),8),5,0,'-'),8,0,'-'))) - (CURRENT DATE) LIKE '-%'

I would like to get the week number of the "FINE PROD." field, but i don't know the correct function

by **sergeyken** » Wed Nov 17, 2021 6:45 pm

(BTW: It is unclear: what is the reason for such sophisticated conversion of your date column DTSCAD?)

You need to use WEEK_ISO function with either type of CORRECT PARAMETER allowed by the function description.

WEEK_ISO

The WEEK_ISO function returns an integer between 1 and 53 that represents the week of the year. The week starts with Monday. Week 1 is the first week of the year to contain a Thursday, which is equivalent to the first week containing January 4. Thus, it is possible to have up to 3 days at the beginning of the year appear as the last week of the previous year or to have up to 3 days at the end of a year appear as the first week of the next year.

WEEK_ISO (expression)

expression

An expression that returns a value of one of the following built-in data types:
a date,
a timestamp,
a character string, or
a graphic string.
If expression is a character or graphic string, its value must be a valid string representation of a date or timestamp. For the valid formats of string representations of dates and timestamps, see String representations of datetime values.

The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value.

Please, present here the example: how did you try to use WEEK_ISO??? :oops:

by **sergeyken** » Wed Nov 17, 2021 6:56 pm

Start from a minimal code which is working. Run it, and demonstrate its ACTUAL RESULT.
Next, add more of your required output data to your code. Run it again, and demonstrate its ACTUAL RESULT.
Repeat these steps, one new element at a time; do not forget to verify the results of EACH STEP, and present it here, if you REALLY need assistance.
Continue until either you are faced an error (then present both the error message, and the code causing this error), or you have got correct results.

This is the TYPICAL WAY of ANY SOFTWARE CODE DEVELOPMENT.

IBM Mainframe Forum

WEEK ISO with Dates in string format YYYYMMDD

WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD

Re: WEEK ISO with Dates in string format YYYYMMDD