Is there some way to set a date pattern and force a specific day?

I have a complicated select with a lot of inner case within case within case statements to try and figure out the right date, but after all is figured out, it needs to use only the first day of the month.

For instance, I might get 08/10/2018, but I want my end result to be 08/01/2018.

Is it possible to do something along the lines of this below (obviously doesn’t work but looking for suggestions).

SELECT TO_CHAR(sysdate,'MM/01/YYYY') FROM DUAL;

​x
 
SELECT TO_CHAR(sysdate,'MM/01/YYYY') FROM DUAL;​

In this case, sysdate would be replaced with a big list of case statements and calculations. The only way I see to do it is do

 TO_CHAR(huge_calculations,'MM')||'01'||TO_CHAR(huge_calculations_again,'YYYY')

 
 TO_CHAR(huge_calculations,'MM')||'01'||TO_CHAR(huge_calculations_again,'YYYY')​

Answer

Here are a few expressions that return the first day of the month for a given date:

Simply:

SELECT TRUNC(sysdate, 'MM') FROM DUAL;

 
SELECT TRUNC(sysdate, 'MM') FROM DUAL;​

Or:

SELECT TRUNC(sysdate) - TO_NUMBER(TO_CHAR(sysdate,'DD')) + 1 FROM dual

 
SELECT TRUNC(sysdate) - TO_NUMBER(TO_CHAR(sysdate,'DD')) + 1 FROM dual​

Or using LAST_DAY:

SELECT ADD_MONTHS((LAST_DAY(sysdate)+1),-1) FROM DUAL;

 
SELECT ADD_MONTHS((LAST_DAY(sysdate)+1),-1) FROM DUAL;​

Demo on DB Fiddle

Advertisement

Answer