sql oracle 当前月份

Question

我能够生成一个查询，该查询将为我提供 10 月份的自动保存信息。

但是，我正在尝试对其进行修改，以便我可以获得当前月份的通用查询，这样我就不必每次都更新查询。

有什么想法吗？

下面是我 2015 年 10 月的查询

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; 

Answer 1

听起来你只是想替换

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

与

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

sysdate返回当前日期。 trunc(<<date>>, 'MM')返回给定输入当月第一天的午夜。