390中求某个月的最后一天?

neo2718

SELECT LAST_DAY('2002/03/15') FROM SYSIBM.SYSDUMMY1
返回2002/03/31
此函数在390平台上才有(DB2 V7 FOR 390)

bbniu

有谁知道在rs6000 db2 v7.2上用什么函数？

mrprogram

要自己做一个用户自定义函数了
-- DB2 UDB UDF(User-Defined Function) Samples for Migration
--
-- 2001/08/29, 11/06, 2002/05/05
--
-- Name of UDF: LAST_DAY (D Date)
--              LAST_DAY (D Timestamp)
--
-- Used UDF: None
--
-- Description: Last day of month.
--
-- Author: TOKUNAGA, Takashi
--

--------------------------------------------------------------------------
CREATE FUNCTION LAST_DAY (D Date)
RETURNS Date
LANGUAGE SQL
SPECIFIC LAST_DAYDate
DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
RETURN
D + 1 month - day(D + 1 month) day
&#59;
---------------------------------------------------
DB20000I The SQL command completed successfully.


--------------------------------------------------------------------------
CREATE FUNCTION LAST_DAY (D Timestamp)
RETURNS Timestamp
LANGUAGE SQL
SPECIFIC LAST_DAYTimestamp
DETERMINISTIC
CONTAINS SQL
NO EXTERNAL ACTION
RETURN
D + 1 month - day(D + 1 month) day
&#59;
---------------------------------------------------
DB20000I The SQL command completed successfully.


--------------------------------------------------------------------------
select sysdate(),
   last_day(sysdate()) "Last",
   last_day(sysdate()) - sysdate() "Days Left"
   from sysibm.sysdummy1
&#59;
---------------------------------------------------

1                          Last                       Days Left              
-------------------------- -------------------------- ----------------------
2002-05-06-08.41.41.723001 2002-05-31-08.41.41.723001        25000000.000000

    1 record(s) selected.


Note: Result of subtracting two timestamps ia a timestamp duration.
      Its data type is DECIMAL(20,6) with format 'yyyymmddhhmiss.ssssss'.  


--------------------------------------------------------------------------
select hiredate, last_day(date(hiredate)) "Last_Date"
  from (values ('1980-10-17')
              ,('1980-01-31')
              ,('1981-02-22')
              ,('1981-03-02')
              ,('1987-04-19')
              ,('1981-05-01')
              ,('1981-06-09')
              ,('1981-07-08')
              ,('1981-09-28')
              ,('1981-11-17')
              ,('1981-12-01')
              ,('1981-12-31')
              ,('1982-01-23')
) q(hiredate)
&#59;
---------------------------------------------------

HIREDATE   Last_Date  
---------- ----------
1980-10-17 1980-10-31
1980-01-31 1980-01-31
1981-02-22 1981-02-28
1981-03-02 1981-03-31
1987-04-19 1987-04-30
1981-05-01 1981-05-31
1981-06-09 1981-06-30
1981-07-08 1981-07-31
1981-09-28 1981-09-30
1981-11-17 1981-11-30
1981-12-01 1981-12-31
1981-12-31 1981-12-31
1982-01-23 1982-01-31

    13 record(s) selected.



--------------------------------------------------------------------------
select last_day(date('2001-1-1')) from sysibm.sysdummy1&#59;
---------------------------------------------------

1         
----------
2001-01-31

    1 record(s) selected.

hatzhang

高啊

Pythagoras

有什么用吗？