This example rounds a date to the nearest month based on the number of days in the month.

 

; A_DT is a single date or an array of date(s) that you need to

; adjust (e.g., DOB field or #DATE in a transformation expression)

 

; Method 1: ----------------------------------------------------

DayLimit:= #FLOOR (0.5*#DAYSINMTH A_DT) &

; 28 ~ 14 => [1-14] 1st of this mth, [15-28] 1st of next mth

; 29 ~ 14 => [1-14] 1st of this mth, [15-29] 1st of next mth

; 30 ~ 15 => [1-15] 1st of this mth, [16-30] 1st of next mth

; 31 ~ 15 => [1-15] 1st of this mth, [16-31] 1st of next mth

 

#IF   DayLimit < #DAY A_DT

#THEN #NEXTBEGMTH A_DT

#ELSE #BEGMTH A_DT #ENDIF

 

; Method 2: ----------------------------------------------------

DayOfMth:=  #DAY A_DT &

DaysInMth:= #DAYSINMTH A_DT &

DayLimit:=  #FLOOR (0.5*DaysInMth) &

 

; if A_DT is less or equal to the day limit, then we want the first

; of the current month. If we subtract the day of the month from

; the date, then we have the end of the previous month. Adding 1

; day gives us the first of this month.

; if A_DT is more than the day limit, then we want the first of 

; the next month. If we subtract DayOfMth from DaysInMth, we have

; the number of days to the end of the current month. Add this to

; A_DT gives us the end of the current month. Adding 1 day gives 

; us the first of the next month. 

; (DayLimit < DayOfMth) is the key to adding/subtracting the 

; correct number of days.

 

1 + A_DT + (DaysInMth*(DayLimit < DayOfMth)) – DayOfMth