How Oracle SQL to get first day of month-CodePudding

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

If the date entered is between 01-nov to 30-nov, then the first day will be 01-nov.
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

CodePudding user response：

You may use a CASE expression here:

SELECT
    dt,
    CASE WHEN EXTRACT(day FROM dt) <= 15
         THEN TRUNC(dt, 'MM')
         ELSE TRUNC(ADD_MONTHS(dt, 1), 'MM') END AS dt_out
FROM yourTable;

CodePudding user response：

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>