How to dis play previous month(Dec) details in db2

nsrinusetty · #1 (**permalink**) 07-07-11, 23:54

Hi,

Please help me for the following problem in db2.

In employee table i want to show the previous month salaray details...but i run this query every month 1st.

Query:

select * from employee where year(salary_date)=year(current timestamp) and month(salary_date)=month(current timestamp)-1;

but here i faced one problem is

For example
case 1:
i want to run this query January month of current year...this time i want to display Last year December ,2010 details.

case 2:

if i want to run this curent year after february the above query working fine

if a run this after feburay the above query get the jan month details.

pls help for above query.

tonkuma · #2 (**permalink**) 07-08-11, 01:38

Apply YEAR and MONTH functions after subtracting 1 month from current date.

Example: Because you are iquiring salary_date, using current date would be enough.

Code:

select * from employee
 where year (salary_date) = year (current date - 1 month)
   and month(salary_date) = month(current date - 1 month)

Another example:

Code:

SELECT *
 FROM  employee
 WHERE salary_date
       BETWEEN LAST_DAY(current date - 2 MONTH) + 1 DAY /* first day of previous month */
           AND current date - DAY(current date) DAYs    /*  last day of previous month */
;

Internet Services

Web Development

Digital Marketing

Consumer Tech