Ad

DATEIME_DIFF Throwing Error When Using Safe Divide

- 1 answer

I've created a query that I'm hoping to use to fill a table with daily budgets at the end of every day. To do this, I just need some simple maths: monthly budget / number of days left in the month.

Then, at the end of the month, I can SUM all of the rows to calculate an accurate budget.

The query is as follows:

SELECT *,
ROUND(SAFE_DIVIDE(budget, DATETIME_DIFF(CURRENT_DATE(), LAST_DAY(CURRENT_DATE()), DAY)),2) AS daily_budget
FROM `mm-demo-project.marketing_hub.budget_manager`

When executing the query, my results present as negative numbers, which according to the documentation for this function, is likely caused by the result overflowing the result type.

View the results of the query.

I've made a fools guess at rounding the calculation. Needless to say that it did not work at all.

How can I stop my query from returning negative number?

Ad

Answer

use below

SELECT *,
ROUND(SAFE_DIVIDE(budget, DATETIME_DIFF(LAST_DAY(CURRENT_DATE()), CURRENT_DATE(), DAY)),2) AS daily_budget
FROM `mm-demo-project.marketing_hub.budget_manager`
Ad
source: stackoverflow.com
Ad