How to get 1st and last day of the month

alluri12 · New User Joined: 15 Aug 2006 Posts: 34

Hi,
I have a query that needs to be changed every month. Could anybody please suggest me how to get this automatically

SELECT T2.ID
FROM TC.TABLE T1,
TC.TABLE T2
WHERE T1.COL1 = T2.COL2
AND T1.BEG_DATE <= '2007-03-31' ---> LAST DAY OF THE PREVIOUS MONTH
AND T1.EXPR_DATE >= '2007-03-01' ---> FIRST DAY OF THE PREVIOUS MONTH.
WITH UR

dick scherrer · Posted: Fri May 11, 2007 2:25 am Post subject:

Hello,

One way would be to have the month presented as a parm or other input. The code would no longer have a literal coded, but would have references to first and last day variables built based on the parm.

If you talk with your scheduling people, they can probably suggest a way to automate this for the monthly run, then nothing need be done manually unless there is some kind of exception run.

William Thompson · Posted: Fri May 11, 2007 2:52 am Post subject:

With COBOL intrinsic functions, you can subtract one from the current month (adjusting for Jan-Dec) and plug in "01" for the first day of last month; And take the current month and plug in an "01" for the first day, convert to integer days, subtract one and convert back and you have the last day of last month.
Saying that, I'm sure DB2 has similar date manipulation functions, haven't you yet found them in the Fine Manual?

dick scherrer · Posted: Fri May 11, 2007 4:46 am Post subject:

Hello,

I was thinking that the scheduling people would know which month the run was for - including any "special" runs and could handle this via the scheduler.

If the job will always be consistent as far as running, you can use:

SELECT CURRENT DATE AS DATE FROM SYSIBM.SYSDUMMY1 which will return yyyy-mm-dd. Then you can set the first and last day as needed.

Here's a link to a bunch of other date/time manipulations - called DB2 Basics: Fun with Dates and Times
http://www-128.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html

alluri12 · New User Joined: 15 Aug 2006 Posts: 34

Thanks Everbody... I am able to resolve this ...... Here is the SQL...

SELECT LAST_DAY(CURRENT_DATE) - 1 MONTH AS DATE1,
LAST_DAY(CURRENT_DATE) - 2 MONTHS + 1 DAY AS DATE2
FROM SYSIBM.LUNAMES

Ravi · New User Joined: 23 May 2003 Posts: 5 Location: Chennai

The below is incorrect. This code will fail if current month has last day as 30th and previous month as 31.

prashant_2420 · New User Joined: 06 Jan 2010 Posts: 2 Location: Oregon

This information proved to be very useful for me. Thanks everybody!

dick scherrer

Hello and welcome to the forum,

Good to hear we have some things that are useful

Enjoy the forum,

d

Ajay Baghel · Posted: Thu Feb 04, 2010 12:37 pm Post subject:

Nice learning.
SELECT LAST_DAY(CURRENT_DATE - 2 MONTHS) AS LASTDAYOFDEC2009,
LAST_DAY(CURRENT_DATE - 3 MONTHS) + 2 DAYS AS SECONDAYOFDEC2009
FROM SYSIBM.SYSDUMMY1;

O/P
LASTDAYOFDEC2009 SECONDAYOFDEC2009
---------------- -----------------
12/31/2009 12/02/2009

Kjeld · New User Joined: 15 Dec 2009 Posts: 82 Location: Denmark