Hi, I'm getting an error doing DateDiff bw two date fields where Nulls exist in one data
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.
Best Answer
-
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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.3
Answers
-
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.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.2 -
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'
0 -
Thank you Mark. Using the PeriodDiff did it for me. Thank you so much.
2 -
@AeyaiSaengkeo that's great to hear! If you can accept any answers that helped you, that will help others in the community.
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.4
Categories
- 10.8K All Categories
- 3 Private Company Board
- 1 APAC User Group
- 12 Welcome
- 39 Domo News
- 9.7K Using Domo
- 1.9K Dataflows
- 2.5K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 343 Workbench
- 260 Domo Best Practices
- 11 Domo Certification
- 464 Domo Developer
- 50 Domo Everywhere
- 105 Apps
- 714 New to Domo
- 85 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 26 お知らせ
- 64 Kowaza
- 299 仲間に相談
- 653 ひらめき共有