Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2009
    Posts
    2

    Unanswered: Problem's Getting Date Function Working?

    Hello!

    We have a process in place which tracks certain "hits" through an ODBC connection - right now, the entire code (which returns what i want) looks like this:

    SELECT A.incdnt_rec_typ AS CATEGORY, COUNT (DISTINCT A.INCDNT_ID) AS QTY, B.DIV_NM_EN AS DIVISION_NAME, c.sts_desc_en AS STATUS

    FROM PQ5.INCDNT_ALL A, PQ5.DIV B, pq5.sts_all c
    WHERE
    A.DIV_ID = B.DIV_ID
    AND a.sts_cd = c.sts_cd
    AND A.incdnt_rec_typ = 'CAR'
    AND date(creat_tmstmp) < '2009-07-01'

    GROUP BY A.incdnt_rec_typ, B.DIV_NM_EN, c.sts_desc_en;

    I want to modify the the code so, simply, instead of having to adjust the "month" every different time i run it (on a monthly basis - i.e.// in august, i'd have to change it to "2009-08-01", it only includes things from the last day of each month, and before - makes sense?

    Any help is appreciated.
    Andrew.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by andmunn
    AND date(creat_tmstmp) < '2009-07-01'
    Turn it into this:

    and creat_tmstmp < timestamp(current date - (day(current date) - 1) days,'00:00:00')

    Andy

  3. #3
    Join Date
    Jul 2009
    Posts
    2
    Works like a charm!

    Just to make sure i understand - you are tellign it to look at anything created before the "date" of today's date - today's date (which would bring you to the first of this month), -1 (which woudl bring you to the last day of the month before.

    CorrecT? Would this inclue things created on the last day of the previous month? I.E.// jun 30/09?
    Andrew.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is incorrect. the DAY function returns the day of the month of the expression. So, for today (7/31/2009), the DAY function return 31. By subtracting 1 from that we get 30. By subtracting 30 days from today's date we get the first of the month. I set the time portion of the timestamp to midnight, so that the resulting timestamp used for comparison is, for today's date would be 2009-07-01-00:00:00.000000. So your comparison would get what you want.

    Andy

  5. #5
    Join Date
    Dec 2008
    Posts
    76
    While this had nothing to do with your specific query, here is another date trick I've found useful:

    select (current date - x.offset days) as saturday_of_this_week
    from table(values(dayofweek(current date) - 7)) as x(offset);
    RD

Posting Permissions

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