Results 1 to 5 of 5

Thread: Fun with Dates

  1. #1
    Join Date
    Dec 2009
    Posts
    9

    Unanswered: Fun with Dates

    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.

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    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
    Last edited by Lenny77; 01-12-10 at 13:01.

  3. #3
    Join Date
    Dec 2009
    Posts
    9
    ok, that gets me current year MTD values?
    What about last year's? surely a "-1" goes somewhere?

  4. #4
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by maw230 View Post
    ok, that gets me current year MTD values?
    What about last year's? surely a "-1" goes somewhere?
    That's give you start of the current month. Nothing else.

    For get start of the current year you have to use: current date - (dayofyear(current date) - 1) days

    Lenny

  5. #5
    Join Date
    Dec 2009
    Posts
    9
    thanks lenny, i will see what i can do with that

Posting Permissions

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