CODEWITHSUNDEEP
sqloracle-database
Riyan Pratama
Riyan Pratama

Reputation: 71

How Oracle SQL to get first day of month

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

Upvotes: 4

Views: 61974

Answers (2)

Imdad Ul Haq
Imdad Ul Haq

Reputation: 1

CREATE TABLE TBL_TIME_QUERY (
  PERIOD      VARCHAR2(50),
  PERIOD_FROM TIMESTAMP,
  PERIOD_TO   TIMESTAMP
);

INSERT INTO TBL_TIME_QUERY (PERIOD) VALUES ('Jan24-Mar24');

     | Jan24-Mar24    |


UPDATE TBL_TIME_QUERY 
SET PERIOD_FROM = 

TO_TIMESTAMP(TO_CHAR(TRUNC(TO_DATE(SUBSTR(TRIM(UPPER(PERIOD)), 1, 5), 'MONRR'), 'MM'), 'DD-MON-RR') || ' 00:00:00', 'DD-MON-RR HH24:MI:SS')

,PERIOD_TO =

TO_TIMESTAMP(TO_CHAR(LAST_DAY(TO_DATE(SUBSTR(TRIM(UPPER(PERIOD)), 7, LENGTH(PERIOD)), 'MONRR')), 'DD-MON-RR') || ' 23:59:59','DD-MON-RR HH24:MI:SS') 
 ;

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142720

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>

Upvotes: 18

Related Questions