Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    3

    Unanswered: How to use date in db2

    Hi,
    I have a table with the following data:

    TABLE TAB2

    NAME HIREDATE
    ------ -----------
    SMITH 12/17/1984
    ALLEN 02/20/1985
    DOYLE 04/04/1985
    DENNIS 05/15/1985

    How to list out the no.of employees joined in every month in ascending order?

    Please help me to achieve this.
    Thanks in advance .

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    select month(HIREDATE), year(HIREDATE), count(*) from TAB2
    group by month(HIREDATE), year(HIREDATE)
    order by year(HIREDATE), month(HIREDATE)
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    TRUNC(hiredate , 'MM') may be another solution.

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.

    CREATE TABLE Something_Report_Periods
    (something_report_name CHAR(10) NOT NULL PRIMARY KEY
    CHECK (something_report_name LIKE <pattern>),
    something_report_start_date DATE NOT NULL,
    something_report_end_date DATE NOT NULL,
    CONSTRAINT date_ordering
    CHECK (something_report_start_date <= something_report_end_date),
    etc);

    These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

    You now have a general tool instead of a proprietary trick for one particular problem!

Posting Permissions

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