Oracle返回的第一天和最后一天ORA-00904：“NOV”：标识符无效

Question

I'm pretty new in Oracle and I want count data from first day and last day of this month (dynamically).

My PHP code look like this :

$date = Date('d-M-y');

// Query
$query = "Select COUNT(*) FROM myTable WHERE CREATED 
          BETWEEN FIRST_DAY(TRUNC(TO_DATE(".$date."), 'MON')) AND 
                  LAST_DAY(TRUNC(TO_DATE(".$date."), 'MON'))";

But I always get the error

ORA-00904: "NOV": invalid identifier

Which NOV is the correct month -right?

Answer 1

If you leave it to oracle you could use

$query = "Select COUNT(*) FROM myTable WHERE TRUNC(SYSDATE,'MON') = TRUNC(CREATED,'MON')";

If you have an index on CREATED it could be faster to use

$query = "Select COUNT(*) FROM myTable WHERE CREATED >=  TRUNC(SYSDATE,'MON') AND CREATED < TRUNC(ADD_MONTH(SYSDATE,1),'MON')