Link to home
Create AccountLog in
Avatar of Larry Brister
Larry BristerFlag for United States of America

asked on

Sysdate and Current Month

Hello,
  I have an Oracle statement that returns product information for the past 120 days.

I need to modift the date part so that I get a current "Month to date" data set.
You can see my sysdate part below.  How do I modify this to get only the current month?

SELECT   invc.acct_dt, part.user_part_no, Sum(inv.qty) as QTY
    FROM invc invc, invc_ln_prod_fam inv, part part
   WHERE (    (invc.invc_no = inv.invc_no)
          AND (inv.part_no = part.part_no)
          AND (   (invc.invc_status_cd = 'OPEN')
               OR (invc.invc_status_cd = 'CLOSED')
              )
          AND (invc.invc_type_cd = 'INV')
              and trunc(invc.acct_dt) > trunc(sysdate -120)
         )
GROUP BY invc.acct_dt, part.user_part_no
ORDER BY part.user_part_no ASC
Avatar of Ivo Stoykov
Ivo Stoykov
Flag of Bulgaria image

Hi lrbrister,

to_number(to_char(SYSDATE, 'yyyymm'))

I thibk it is better to include year too because ortherwise feb 2006 will be less than dec 2005

HTH

I
Avatar of Larry Brister

ASKER

ivostoykov,
  I am a SQLServer guy.  Could you please place that code in context with the code below?

and trunc(invc.acct_dt) > trunc(sysdate -120)
Hi lrbrister,

> and trunc(invc.acct_dt) > trunc(sysdate -120)
and to_number(to_char(trunc(invc.acct_dt, 'yyyymm'))) > to_number(to_char(trunc(sysdate -120, 'yyyymm')))

I'm not sure whether you need trunc - it rounds date to 16th date - but it is up to you... take a look

anyway here is without trunc
and to_number(to_char(invc.acct_dt, 'yyyymm')) > to_number(to_char(sysdate -120, 'yyyymm'))

regards

I
ivostoykov,
  I appreciate your quick response.  Maybe I'm not being clear...my fault.

I just want data returned for the current month.  I don't want the "120" calculation returned or used at all.

So whatever the day it is this month...I just get March 2006 data and March 2006 only.

Same applies once the month or April comes.  Whether it's the 1st or the 30th I get the sum total of April data....once Sysdate is within April.
ASKER CERTIFIED SOLUTION
Avatar of Ivo Stoykov
Ivo Stoykov
Flag of Bulgaria image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
ivostoykov,
  That's the ticket!  Great job.  Thanks and points being awarded now.;