Calculate the Last Day of the Previous Month

 

How to calculate the last day of the previous month in query via SQL?

Solution

Use CASE logic to do this, logic is as follows :

If  Leap Year
If month = 1
date = (year - 1) * 10000 + 1231
else
date = year * 10000 + (month - 1) + day (dependent of month, if month = march
==> day = 29)
else
If month = 1
date = (year - 1) * 10000 + 1231
else
date = year * 10000 + (month - 1) + day (dependent of month, if month = march
==> day = 28)

Select clause to calculate the last day of the month preceding the date stored in DTEINV :

SELECT
CASE
 WHEN MOD ( YEAR ( Date ( DTEINV, YYMMDD ) ), 4 ) = 0 THEN
  CASE MONTH ( Date ( DTEINV, YYMMDD ) )
   WHEN 1 THEN date ( ( YEAR ( Date ( DTEINV, YYMMDD ) ) - 1 ) * 10000 + 1231,
   YYYYMMDD )
   ELSE date ( YEAR ( Date ( DTEINV, YYMMDD ) ) * 10000 + ( MONTH ( Date ( DTEINV,
   YYMMDD ) ) - 1 ) * 100 +
   CASE MONTH ( Date ( DTEINV, YYMMDD ) )
    WHEN 2 THEN 31
    WHEN 3 THEN 29
    WHEN 4 THEN 31
    WHEN 5 THEN 30
    WHEN 6 THEN 31
    WHEN 7 THEN 30
    WHEN 8 THEN 31
    WHEN 9 THEN 31
    WHEN 10 THEN 30
    WHEN 11 THEN 31
    WHEN 12 THEN 30
   END, YYYYMMDD )
 END
ELSE
CASE MONTH ( Date ( DTEINV, YYMMDD ) )
 WHEN 1 THEN date ( ( YEAR ( Date ( DTEINV, YYMMDD ) ) - 1 ) * 10000 + 1231,
  YYYYMMDD )
  ELSE date ( YEAR ( Date ( DTEINV, YYMMDD ) ) * 10000 + ( MONTH ( Date ( DTEINV,
  YYMMDD ) ) - 1 ) * 100 +
  CASE MONTH ( Date ( DTEINV, YYMMDD ) )
   WHEN 2 THEN 31
   WHEN 3 THEN 28
   WHEN 4 THEN 31
   WHEN 5 THEN 30
   WHEN 6 THEN 31
   WHEN 7 THEN 30
   WHEN 8 THEN 31
   WHEN 9 THEN 31
   WHEN 10 THEN 30
   WHEN 11 THEN 31
   WHEN 12 THEN 30
  END, YYYYMMDD )
 END
END AS COLUMN0000
FROM
SCDB1.INVHDR INVHDR

Here is another example that will also work:
Here is a sample on SCDB1 also:

SELECT
 DATE( month( Date( SHPDTE, YYMMDD ) ) || '01' || year( Date( SHPDTE, YYMMDD )), MMDDYYYY, CHAR ) 
 - 1 day AS COLUMN0000,
 Date( SHPDTE, YYMMDD ) AS SHPDTE
FROM
 SCDB1.INVDET INVDET

Ref#: 1478000