cancel
Showing results for 
Search instead for 
Did you mean: 

Days in a month

Former Member
0 Kudos

Hie guys

I have the below formula that should pull the day in the next month. When I run crystal reports it gives the error "A day number must be between 1 and the number of days in a month crystal reports error" I assume that is because 31 does not exist in the current or next month, for instance there is no 31 June. How do I modify the below formula to consider the days in a month.

IF ISNULL({CASE_EXTRA.FIELD055_DATE}) OR {CASE_EXTRA.FIELD055_DATE}=DATE(0,0,0)  THEN

    IF PrintDate > DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),{DEBITORDERARRANGEMENT.DAYOFMONTH}) THEN

        DATE(DatePart ("yyyy",PrintDate ),(DatePart ("m",PrintDate ) + 1),{DEBITORDERARRANGEMENT.DAYOFMONTH})

    ELSE

        DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),{DEBITORDERARRANGEMENT.DAYOFMONTH})

ELSE

    IF PrintDate > {CASE_EXTRA.FIELD055_DATE} THEN

        DATE(DatePart ("yyyy",{CASE_EXTRA.FIELD055_DATE} ),(DatePart ("m",{CASE_EXTRA.FIELD055_DATE} ) + 1),DatePart ("d",{CASE_EXTRA.FIELD055_DATE} ))

    ELSE

        {CASE_EXTRA.FIELD055_DATE}

;

Accepted Solutions (0)

Answers (1)

Answers (1)

former_member205840
Active Contributor
0 Kudos

Hi Star,

I think there is some problem with this filed : {DEBITORDERARRANGEMENT.DAYOFMONTH}, this field contains a number which is not falling in 1 to 31.  or it could be null

Thanks,

Sastry

Former Member
0 Kudos

Hie Sastry

If it is null how do I go about it and if its because there is no 31st in the next month.

former_member205840
Active Contributor
0 Kudos

Hi Star,

If you are manipulating date, then it should never be NULL or contains Alphabets.  This will give an error while manipulating date part.

Please try to use below logic when you add or reduce months or years.

DATE(year(Dateadd('m',+2,printdate)),Month(Dateadd('m',+2,printdate)),01)-1

If you look at your formula for adding a month :

DATE(DatePart ("yyyy",PrintDate ),(DatePart ("m",PrintDate ) + 1),{DEBITORDERARRANGEMENT.DAYOFMONTH})

suppose if you are trying to add one month for 12/31/2016, then the above formula will add one month for month part then it will be 13 not 01 and also year will be of 2016 only it will not be 2017. so, always use DateAdd() when you are adding any value to date.

See below formula, I have replaced '{DEBITORDERARRANGEMENT.DAYOFMONTH}' with Printdate.



IF ISNULL({CASE_EXTRA.FIELD055_DATE}) OR {CASE_EXTRA.FIELD055_DATE}=DATE(0,0,0)  THEN

    IF PrintDate > DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),day(currentdate)) THEN

       

    DATE(year(Dateadd('m',+2,printdate)),Month(Dateadd('m',+2,printdate)),01)-1

    ELSE

    DATE(DatePart ("yyyy",PrintDate ),DatePart ("m",PrintDate ),day(currentdate))

        ELSE

        IF PrintDate > {CASE_EXTRA.FIELD055_DATE} THEN

                DATE(DatePart ("yyyy",{CASE_EXTRA.FIELD055_DATE} ),(DatePart ("m",{CASE_EXTRA.FIELD055_DATE} ) + 1),DatePart ("d",{CASE_EXTRA.FIELD055_DATE} ))

                ELSE

                {CASE_EXTRA.FIELD055_DATE}

Thanks,

Sastry