First of Month, Last of Month in YYYYMMDD

[jconway]

Hello,

I'm pretty new to this DB2 SQL and am having trouble putting together the code for the following items. I need to grab the first and last of the previous month in the format YYYYMMDD. If anyone has this lying around somewhere or could give me some quick insight, I'd appreciate it.

Thanks!

 

[MarcLodge]

jconway,

Try the following. It's slightly convoluted, but it will cope with January where the previous month is in a different year. The substr assumes that your standard date format is CCYY-MM-DD. If your site differs from this, you'll have to change the positions in order to pick up the correct data.

SELECT SUBSTR(CHAR(BEGMONTH),1,4)||SUBSTR(CHAR(BEGMONTH),6,2)||           
       SUBSTR(CHAR(BEGMONTH),9,2)                                         
      ,SUBSTR(CHAR(ENDMONTH),1,4)||SUBSTR(CHAR(ENDMONTH),6,2)||           
       SUBSTR(CHAR(ENDMONTH),9,2)                                         
FROM                                                                      
(SELECT CURRENT_DATE - DEC(DAY(CURRENT_DATE)-1) DAYS - 1 MONTH AS BEGMONTH
      ,CURRENT_DATE - DEC(DAY(CURRENT_DATE)) DAYS AS ENDMONTH             
FROM SYSIBM.SYSDUMMY1) AS T_DATE

Hope this helps

Marc