WEEK ISO with Dates in string format YYYYMMDD



IBM's flagship relational database management system

WEEK ISO with Dates in string format YYYYMMDD

Postby 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.
ANGELDANY
 
Posts: 4
Joined: Tue Nov 16, 2021 4:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby 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.
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby 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.

Image
ANGELDANY
 
Posts: 4
Joined: Tue Nov 16, 2021 4:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby 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.

Image

And what does this have to do with mainframes???
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 621
Joined: Wed Mar 11, 2009 12:22 am
Location: Vilnius, Lithuania
Has thanked: 3 times
Been thanked: 28 times

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby 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?
ANGELDANY
 
Posts: 4
Joined: Tue Nov 16, 2021 4:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby 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.

Image


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??
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby ANGELDANY » Wed Nov 17, 2021 1:28 pm

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
ANGELDANY
 
Posts: 4
Joined: Tue Nov 16, 2021 4:55 pm
Has thanked: 0 time
Been thanked: 0 time

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby 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:
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times

Re: WEEK ISO with Dates in string format YYYYMMDD

Postby 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.
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 235
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 5 times
Been thanked: 21 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post