How Oracle SQL to get first day of month

Question

Requirement is input date should produce first day of the month. Condtions are:

1. If the date entered is between 01-nov to 30-nov, then the first day will be 01-nov.
2. for all other/next month it should return 01st day of corresponding month.

Ex: if i select Month OCT and Year 2021 then Startinvoice same with startbilldate and endinvoice get lastday of month from startinvoice but when i select Month NOV and Year 2021 then Startinvoice = 01 nov 2021 and endinvoice = 30 nov 2021 next month it should return 01st day of corresponding month. enter image description here

Answer 1

Those conditions are, basically, equal. November has 30 days anyway, so your 1st condition is contained in the 2nd one. No difference at all.

Therefore, you'd just truncate date value to month, e.g.

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> select sysdate,
  2         trunc(sysdate, 'mm') first_of_month
  3  from dual;

SYSDATE             FIRST_OF_MONTH
------------------- -------------------
21.12.2021 09:01:22 01.12.2021 00:00:00

SQL>