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

 

 

 

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: April 21, 2017