How to calculate the last day of the previous month in query via SQL?
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.