Hi Folks,

New to DB2 so I am trying to find out how to exclude weekends in a query. In sql it is...

Where (DATEPART(dw, Testdatetime) > 1) AND (DATEPART(dw, Testdatetime) < 7)

...but I cannot for the life of me figure out, or find online how to do this in DB2.

Can someone be as so kind as to provide me the syntax?

Thanks

DAYOFWEEK(Testdatetime) - return range 1-7, where 1 represents Sunday.

DAYOFWEEK_ISO(Testdatetime) - range 1-7, where 1 represents Monday.

So would that be...

DAYOFWEEK(Testdatetime) - return range 2-6

...if I want to exclude Sat and Sun?

Thanks

yeh , right

The full query is...
SELECT start_date, tran_id, SUM(MIPS + IMS_MIPS) AS MIPS, SUM(tran_count) AS tran_count, SUM(resp_sec) AS resp_sec, SUM(CPU_sec) AS CICS_CPU_sec,
SUM(IMS_CPU_sec) AS IMS_CPU_sec, SUM(MIPS) AS CICS_MIPS, SUM(IMS_MIPS) AS IMS_MIPS
FROM (SELECT start_date, CASE WHEN tran_id IN ('FLOE', 'APOE', 'UW70', 'IR02', 'DRIV', 'DOCC', 'UWDR', 'MXPF', 'LDPF', 'POIN', 'LCLO', 'GFES', 'WFST',
'GATE', 'CONL', 'UP1K', 'RTEM', 'ULET', 'IL65', 'INSR', 'WRKS', 'TAX1', 'EMUL', 'LDPC', 'LPLI', 'L70I', 'CSNC', 'LEXM', 'EM1Q', 'LPBR',
'UPAT', 'EC01', 'DNTN', 'EDDD', 'FXDD', 'ALLB', 'IL60', 'GFGT', 'APBB', 'LMST') THEN tran_ID ELSE '*Other' END AS tran_id, tran_count,
resp_sec, CPU_sec, IMS_CPU_sec, MIPS, IMS_MIPS
FROM DZ001.dZtvcidA_mips
WHERE (start_date >= ?) AND (start_date < ?) AND (mvs_sys_id = '0600') AND (region_id IN ('PRDA', 'PRD2', 'PRD3', 'PRD4', 'PRD5', 'PRD6',
'PRD7', 'PRD8', 'PRD9'))) a
GROUP BY start_date, tran_id
ORDER BY 1, 2

came back as invalid. is there a particular place within the query it needs to go? I put it in the 'Where' portion and it came back invalid.

it has to go in the where clause .. Did you check in a simple query ???

what version and platform are you on ???

can you post the 'invalid' query and the message too

Derr...I found my issue, I used the wrong datetime field name...I wrote it out like (DAYOFWEEK(start_date) > 1) AND (DAYOFWEEK(start_date) < 7) and it worked perfectly. Thanks so much for your help Sathyaram!