Larry Brister
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
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
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)
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(in vc.acct_dt , 'yyyymm'))) > to_number(to_char(trunc(sy sdate -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.acc t_dt, 'yyyymm')) > to_number(to_char(sysdate -120, 'yyyymm'))
regards
I
> and trunc(invc.acct_dt) > trunc(sysdate -120)
and to_number(to_char(trunc(in
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.acc
regards
I
ASKER
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.
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
ivostoykov,
That's the ticket! Great job. Thanks and points being awarded now.;
That's the ticket! Great job. Thanks and points being awarded now.;
to_number(to_char(SYSDATE,
I thibk it is better to include year too because ortherwise feb 2006 will be less than dec 2005
HTH
I