Fun with Dates

maw230 · #1 (**permalink**) 01-12-10, 11:43

As an extremely new user to DB2 syntax, I am still having a difficult time with the DATE functions. I need a month-to-date function that I can write in a pass through query in Access to a DB2 server. I have received great help with a year -to-date function from this forum, and one would think changing to MTD wouldn't be such a leap. Here is the SQL for the year-to-date query that forum users helped me with:

Code:

SELECT LINE, CASH_UNITS,
CHARGE_UNITS, CASH_SALES, 
CHARGE_SALES, CASH_SALES_AT_COST,  
CHARGE_SALES_AT_COST
FROM DWQRYDATA.SLSDSI
Where 
DATE_ID BETWEEN 
current date - (dayofyear(current date) - 1)  days - 1 year  
    AND CURRENT_DATE - 1 YEAR
group by LINE, CASH_UNITS,  CHARGE_UNITS,  
CASH_SALES, CHARGE_SALES, 
CASH_SALES_AT_COST,  
CHARGE_SALES_AT_COST
order by LINE

Now, what I need is a Month-to-date function that will bring back this years and last year's month-to-date values. I assume this would be done with a: BETWEEN [this year functions] OR BETWEEN [last year functions]. Please help as I think using the DB2 server to do my calculations would help the performance of my query greatly, as compared to using ODBC links through Access. Thanks.

Lenny77 · #2 (**permalink**) 01-12-10, 11:56

instead of current date - (dayofyear(current date) - 1) days - 1 year ,
use current date - (day(current date) - 1) days

Also, you can use last_day(current date - 1 month) + 1 day.

Lenny