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.