First_Day/Last_Day Query

Question

Could you please help me to correct the script as below:

set LastDay=SELECT LAST_DAY(SYSDATE) FROM dual;
set FirstDay=Select trunc((sysdate),'month') as First_day_of_month from dual;

SELECT count(*) FROM tab1 g , h.ab1 LEFT JOIN tab2 h ON g.bba = h.bba 
WHERE 1 = 1
AND g.DATE_ BETWEEN TO_DATE('FirstDay', 'YYYYMMDD') AND TO_DATE('LastDay', 'YYYYMMDD');

Answer 1

In Oracle, a DATE ALWAYS has year, month, day, hour, minute and second components. Using LAST_DAY(SYSDATE) only sets the year-month-day component of a date and does not modify the time component so if you filter from the start of the month to LAST_DAY(SYSDATE) then you will exclude any values from the last day of the month with a time component between the current time and 23:59:59.

What you want is to use:

SELECT count(*)
FROM   tab1 g
       CROSS JOIN h.ab1 -- your query is confusing around the joins
                        -- and may need fixing
       LEFT JOIN tab2 h
       ON g.bba = h.bba 
WHERE  g.DATE_ >= TRUNC(SYSDATE, 'MM')
AND    g.DATE_ <  ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1);