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.
**Check out my Domo Tips & Tricks Videos
**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.
**Check out my Domo Tips & Tricks Videos
**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 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.3 -
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.
**Check out my Domo Tips & Tricks Videos
**Make sure toany users posts that helped you.
**Please mark as accepted the ones who solved your issue.4
Categories
- 10.5K All Categories
- 7 Connect
- 917 Connectors
- 250 Workbench
- 464 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 477 Datasets
- 190 Visualize
- 252 Beast Mode
- 2.1K Charting
- 11 Variables
- 17 Automate
- 354 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 246 Distribute
- 62 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 173 Product Ideas
- 1.2K Ideas Exchange
- 12 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive