Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2009
    Posts
    2

    Unanswered: date functions help

    Hi DB2geeks!

    I am new to DB2 practising in DB2 v 8.0 Windows Environment. Part of the table looks like as follows:-
    doi(column) dot(column)
    04/10/1996 05/10/1996
    04/12/1996 05/05/1996
    04/21/1996 05/15/1996
    04/25/1996 05/25/1996
    04/30/1996 05/22/1996
    Request to provide me the solution for the following questions?
    1)Display the last date of the month in dot

    2)Display the months between the dot & doi

    3)Display the next occurence of "friday" to the dot

    4)Display the rounded date in the year format from the day in dot

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a good article:
    DB2 Basics: Fun with Dates and Times

    an example for your request:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     sample_data(doi, dot) AS (
    SELECT DATE(doi), DATE(dot)
      FROM (VALUES
             ('04/10/1996', '05/10/1996')
            ,('04/12/1996', '05/05/1996')
            ,('04/21/1996', '05/15/1996')
            ,('04/25/1996', '05/25/1996')
            ,('04/30/1996', '05/22/1996')
           ) AS s(doi, dot)
    )
    SELECT doi, dot
         , dot + 1 MONTH - DAY(dot + 1 MONTH) DAYS
             AS "Last date of month"
         , TIMESTAMPDIFF(64, CHAR(TIMESTAMP(dot, '00:00:00') - TIMESTAMP(doi, '00:00:00')))
             AS "Months between"
         , dot + MOD(13 - DAYOFWEEK(dot), 7) DAYS
             AS "Next Friday"
         , DAYOFYEAR(dot)
             AS "Date in year"
      FROM sample_data
    ;
    ------------------------------------------------------------------------------
    
    DOI        DOT        Last date of month Months between Next Friday Date in year
    ---------- ---------- ------------------ -------------- ----------- ------------
    1996-04-10 1996-05-10 1996-05-31                      1 1996-05-10           131
    1996-04-12 1996-05-05 1996-05-31                      0 1996-05-10           126
    1996-04-21 1996-05-15 1996-05-31                      0 1996-05-17           136
    1996-04-25 1996-05-25 1996-05-31                      1 1996-05-31           146
    1996-04-30 1996-05-22 1996-05-31                      0 1996-05-24           143
    
      5 record(s) selected.
    I afraid that I misunderstood your requirements, because of my poor English capability.
    If you want another results, please supply your required results.

    DB2 V8 for Windows is obsolete.
    Here is newest DB2 Express-C:
    Download : IBM DB2 Express-C 9

  3. #3
    Join Date
    Jun 2002
    Posts
    15
    Last day of month and next day can also be expressed as

    Code:
    WITH
     sample_data(doi, dot) AS (
    SELECT DATE(doi), DATE(dot)
      FROM (VALUES
             ('04/10/1996', '05/10/1996')
            ,('04/12/1996', '05/05/1996')
            ,('04/21/1996', '05/15/1996')
            ,('04/25/1996', '05/25/1996')
            ,('04/30/1996', '05/22/1996')
           ) AS s(doi, dot)
    )
    select doi, dot
     ,NEXT_DAY(dot, 'Friday') as "Next Friday"
     ,LAST_DAY(dot) as "Last date of month"
    from sample_data
    Code:
    DOI        DOT        Next Friday Last date of month
    ---------- ---------- ----------- ------------------
    04/10/1996 05/10/1996 05/17/1996  05/31/1996        
    04/12/1996 05/05/1996 05/10/1996  05/31/1996        
    04/21/1996 05/15/1996 05/17/1996  05/31/1996        
    04/25/1996 05/25/1996 05/31/1996  05/31/1996        
    04/30/1996 05/22/1996 05/24/1996  05/31/1996        
    
      5 record(s) selected.

  4. #4
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    Quote Originally Posted by yvanroy
    ,NEXT_DAY(dot, 'Friday') as "Next Friday"
    ,LAST_DAY(dot) as "Last date of month"
    Is this on 9.7 LUW ?
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  5. #5
    Join Date
    Jun 2002
    Posts
    15
    Yes, it is on LUW 9.7 only.

  6. #6
    Join Date
    Jun 2002
    Posts
    15
    Let me clarify my previous statement. The 2 functions works in LUW starting at version 9.7 both they are also available on z/os Version 8.

  7. #7
    Join Date
    Sep 2009
    Posts
    2
    On inserting the query
    ,NEXT_DAY(dot, 'Friday') as "Next Friday"
    ,LAST_DAY(dot) as "Last date of month" the message shows as folows:-

    SQL0440N No authorized routine named "NEXT_DAY" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please see yvanroy's second and third answers.

Posting Permissions

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