Hi, I'm getting an error doing DateDiff bw two date fields where Nulls exist in one data

AeyaiSaengkeo · 2022-03-10T14:39:27+00:00

There was an error rendering this rich post.

I believe my error popped up when I was trying to complete top two formulas. The Start Date field has some null values and where they're null, we're putting an arbitrary future date in so we can do a calc bw the Start Date and Payment Processing Month field. Thank you in advance.

Find more posts tagged with

Accepted answers

MarkSnodgrass

I was suggesting re-writing total months on SaaS to this:

CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
DATEDIFF(DATE(`Payment Processing Month`),DATE(`Start Date`)
END

However, since you are wanting the total months and not the number of days, you will need to do it slightly different. You can use the Period_Diff function to calculate the month difference like this:

CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
PERIOD_DIFF(DATE_FORMAT(`Payment Processing Month`,'%Y%m')DATE_FORMAT(`Start Date`,'%Y%m')
END

Hope this helps.

All comments

MarkSnodgrass

I believe your error is coming using the date_format inside the datediff. Your format that you are changing it to is not a valid date date format. Is Payment Processing Month not an actual date? I would try wrapping the date function around payment processing month in your 2nd formula as well as start date and see if the error goes away.

AeyaiSaengkeo

Thanks for responding Mark. The Payment Processing Month is an actual date. Essentially what we're doing is putting the dates e.g., 2018-02-18 in both columns into Year-Month format so we can calculate the difference in whole months. Does that help? Thanks for the help!

Also if I tried your suggestion are you saying the date function as in the Date_Format function? I've pasted the formulas below

TOTAL months on SaaS: CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE

DATEDIFF(DATE_FORMAT(`Payment Processing Month`,'%y%m'),DATE_FORMAT(`Start Date`,'%y%m'))

Saas Start and is 90 days bw Pmt stmt:

CASE WHEN DATEDIFF(`First Statement Date`,`Start Date`) <= 90 THEN 'Within 90' ELSE

'Greater > 90 Days'

MarkSnodgrass

I was suggesting re-writing total months on SaaS to this:

CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
DATEDIFF(DATE(`Payment Processing Month`),DATE(`Start Date`)
END

CASE WHEN `Start Date` = '2050-01-01' THEN 0 ELSE
PERIOD_DIFF(DATE_FORMAT(`Payment Processing Month`,'%Y%m')DATE_FORMAT(`Start Date`,'%Y%m')
END

Hope this helps.

AeyaiSaengkeo

Thank you Mark. Using the PeriodDiff did it for me. Thank you so much.

MarkSnodgrass

@AeyaiSaengkeo that's great to hear! If you can accept any answers that helped you, that will help others in the community.