get first day of month and first day of prev month

[B.Hauser]

Barbara, you are right! This happens if you only reade half of the post.
I just corrected my solutions

[Barbara Morris]

Birgitta, your code only gets the first day of the current month. The requirement is to get the first day of the previous month if today is the first day of the month.

I think a good way to solve this is to start by subtracting 1 day from the given date. So instead of working on 2020-04-02 and 2020-04-1, your code would work on 2020-04-01 and 2020-03-31. Then, find the first day of the month for that new date.

This is your first SQL statement with the red part added.

Exec SQL Set :FirstDay = Last_Day(:HostDate [COLOR=#FF0000][B]- 1 day[/B][/COLOR] - 1 Month) + 1 day;

[B.Hauser]

Here is the SQL Version:

Exec SQL Set :FirstDay = Last_Day(:HostDate - 1 Month - 1 day) + 1 day;

Birgitta

[JonBoy]

And needless to say I'd wrap it up as a subprocedure so the poor devils who have to maintain my code later don't have to understand it!

[JonBoy]

Your version will work but given you second requirement I'd do it this way:

dayNum = %subdt( todayDate);
If dayNum = 1;
  firstDay = todayDate - %Months(1);
Else;
  firstDay = todayDate - %Days(dayNum - 1);
EndIf;

And no doubt Birgitta or someone will come up with an SQL version.

[castle123]

I'm sure this is on here somewhere I just cant find it, I need to get first day of month which I think is firstDay = todayDate - %Days(%subdt( todayDate:*days)-1);
but if today is the first of month I need to get the prev month first day of month, the job I'm running uses previous day for its updates