[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Sysdate and Current Month

Posted on 2006-03-27
6
Medium Priority
?
5,679 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:lrbrister
  • 3
  • 3
6 Comments
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16300081
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
0
 

Author Comment

by:lrbrister
ID: 16300109
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)
0
 
LVL 22

Expert Comment

by:Ivo Stoykov
ID: 16300140
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:lrbrister
ID: 16300175
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.
0
 
LVL 22

Accepted Solution

by:
Ivo Stoykov earned 2000 total points
ID: 16300207
Hi lrbrister,

> h
then you need
and to_number(to_char(invc.acct_dt, 'yyyymm')) = to_number(to_char(sysdate, 'yyyymm'))

for March 2006 data and March 2006 the result will be
200603 (your field) = 200603 (today) and will return true
if your field is from las year then it will looks like
200503 (your field) = 200603 (today) and will return false

hope it is clear...


regards

I
0
 

Author Comment

by:lrbrister
ID: 16300265
ivostoykov,
  That's the ticket!  Great job.  Thanks and points being awarded now.;
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses
Course of the Month20 days, 11 hours left to enroll

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question