CODEWITHSUNDEEP
sqloracle-database
thiagovargasrj
thiagovargasrj

Reputation: 35

sql oracle current month

I was able to generate a query that would give me the auto saved for the month of October.

However, I'm trying to modify it so I can have a general one for the current month so I don't have to update the query every time.

Any ideas?

Below my query for October, 2015

SELECT
c.invoicenum,c.invoicedate, c.vendorname,c.account, c.invoiceid , decode(nvl(b.invoiceid,-1),-1,'No','Yes') "Approved",
decode(nvl(b.invoiceid,-1),-1,'No',nvl(a.invoiceid,-2),'No','Yes') "Auto Saved for Approval"
FROM 
(SELECT d.invoicenum,d.invoicedate, v.vendorname,d.account, d.invoiceid FROM invoice d, vendor v
WHERE d.invoicestatus in (1,5,7,8) AND v.vendorid=d.vendorid AND v.status=0 AND to_char(d.dateentered,'MMYY')='1015') c, 

(SELECT d.invoicenum,d.invoicedate, v.vendorname,d.account, d.invoiceid FROM invoice d, vendor v
WHERE d.invoicestatus in (7,8) AND v.vendorid=d.vendorid AND v.status=0 AND to_char(d.dateentered,'MMYY')='1015') b, 

(SELECT d.invoicenum,d.invoicedate, v.vendorname,d.account, d.invoiceid FROM invoicehistory b, invoice d, vendor v 
WHERE operationtype=21 AND d.invoicestatus in (7,8) AND b.invoiceid =d.invoiceid AND to_char(d.dateentered,'MMYY')='1015' AND v.vendorid=d.vendorid and v.status=0) a 

WHERE c.invoiceid=b.invoiceid and c.invoiceid=a.invoiceid(+) and a.invoiceid is null;

Upvotes: 2

Views: 2749

Answers (2)

Justin Cave
Justin Cave

Reputation: 231851

It sounds like you just want to replace

to_char(d.dateentered,'MMYY')='1015'

with

trunc( d.dateEntered, 'MM' ) = trunc( sysdate, 'MM' )

sysdate returns the current date. trunc(<<date>>, 'MM') returns midnight on the first of the month of the given input.

Upvotes: 3

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions172.htm

change 

AND to_char(d.dateentered,'MMYY') ='1015'

For

AND to_char(d.dateentered,'MMYY') =  to_char(SYSDATE,'MMYY')

Upvotes: 2

Related Questions