WEEK ISO with Dates in string format YYYYMMDD 1

[ANGELDANY]

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

 

[mikrom]

Hi ANGELDANY,

When you create from your date YYYYMMDD the ISO-Date format YYYY-MM-DD, then you can apply on it the function WEEK_ISO()

Example:

with MYTAB (MYDATE) as (
  values ('20210716'), ('20211116')
)
select 
  MYDATE, 
  substr(MYDATE, 1, 4) || '-' || substr(MYDATE, 5, 2) || '-' || substr(MYDATE, 7, 2) as MYDATE_ISO,
  week_iso(substr(MYDATE, 1, 4) || '-' || substr(MYDATE, 5, 2) || '-' || substr(MYDATE, 7, 2)) as WEEK_ISO    
from MYTAB

Output:

MYDATE         MYDATE_ISO   WEEK_ISO
20210716       2021-07-16         28
20211116       2021-11-16         46

 

[ANGELDANY]

Thanks for the reply mikrom,I have another question.
The query is the one below,i would like to know the week number of the "FINE PROD." field, can you help me? I wrote the query in Excel

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 '-%'

 

[mikrom]

Hi ANGELDANY,

if you want the week number of FINE PROD, then add to your select this:

week_iso(date(insert(insert(left(char(DTSCAD),8),5,0,'-'),8,0,'-'))) as "week number of FINE PROD"​

I tried this example:

with MGARTF (DTSCAD) as (
  values (20210716), (20211116)
)
select 
 date(insert(insert(left(char(DTSCAD),8),5,0,'-'),8,0,'-')) as "FINE PROD",
 week_iso(date(insert(insert(left(char(DTSCAD),8),5,0,'-'),8,0,'-'))) as "week number of FINE PROD"
from MGARTF

Output:

FINE PROD    week number of FINE PROD
2021-07-16                         28
2021-11-16                         46

 

[ANGELDANY]

You are great, it works