Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    5

    Unanswered: Day of Week selection

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    DAYOFWEEK(Testdatetime) - return range 1-7, where 1 represents Sunday.

    DAYOFWEEK_ISO(Testdatetime) - range 1-7, where 1 represents Monday.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jul 2009
    Posts
    5
    So would that be...

    DAYOFWEEK(Testdatetime) - return range 2-6

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

    Thanks

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    yeh , right

    Quote Originally Posted by Xtrout
    So would that be...

    DAYOFWEEK(Testdatetime) - return range 2-6

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

    Thanks
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Jul 2009
    Posts
    5
    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

  6. #6
    Join Date
    Jul 2009
    Posts
    5
    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.

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Jul 2009
    Posts
    5
    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!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •