Error with datetime_add and _sub

Looker Q&A Looker Studio
1

A few months ago all of my data sources was modified (probably after some backend update on the looker side) and all of my date fields converted to datetime. I was able to convert them back but since then i almost can not work with datetime_add and datetime_sub functions (i will use DTA and DTS abbreviations further occurances in this text).
So when i’m trying to ‘DTS(date_created, interval 1 day)’ it works just fine, but right after i’m changing the integer part to another field (i.e. new field WD that consist of formula ‘weekday(date_created)’) so that my formula becomes ‘DTS(date_created, interval WD day)’ it suddenly stops working and all of my charts give me this error:
Looker Studio cannot connect to your data set.

Failed to fetch data from the underlying data set

Error ID: 1b01a3fe

I’ve noticed that the charts are crashing even when i’m adding both new fields ‘DTS(date_created, interval 1 day)’ and ‘DTS(date_created, interval WD day)’ even though there is plain integer (and not field) part in DTS formula. But both of this fields works when they are added to chart separately.

This problem only started in around june-july, because before this time i had multiple fields with DTS expressions and field part in them and they all worked fine.

I’ve tried everything:

  1. putting not field but the expression itself in the integer part (i.e. datetime_sub(date, interval weekday(date_created) day))
  2. using different types of field (all of them was in the date&time “folder”)
  3. using cast function in expression
  4. trying fresh new test data source

Nothing worked, even if it worked it wouldn’t have explained why the error appears even when im trying to add two one-function fields. So i’ve tried to work around that and found that the only two functions that somehow messes with each other is the datetime_sub() (or datetime_add) and weekday(). When i’m adding field with DTS() and year() — works fine, when DTS() and format_datetime(‘%u’, date_created) — works fine too. But still doesn’t work when i’m combining ‘DTS(date_created, interval cast(format_datetime(‘%u’, date_created) as number) day)’.

So i’m asking for someone’s help, please explain to me, what is this **** and how should i deal with this?

2

Here comes a quick update. I’ve done some digging and found the same problem in datetime_sub - Looker and Google Analytics

It is not documented but to use a dynamic interval, you have to cast the value as an Integer and not just use the number outcome of weekday function.

datetime_sub(Date,interval CAST(WEEKDAY(Date) AS INT64) DAY)

It’s so hilarious i didn’t believe it, because i know for a fact that a few months ago casting to int wasn’t necessary.

I’ve tried this and this worked, but only when i’m NOT using weekday() at all in any way, so i’m changing everything to format_datetime(‘%u’, Date) and cast() on top of that. But it’s still working only on my test data source and not in ‘production’ given that they are both from google sheets, both have “date” types.

And even this ‘so-so’ solution doesn’t explain why datetime_sub() and weekday() funcions when used separately in different fields crashes my chart when both added to chart.

In this case i hope someone would explain, why this behavior was changed and how to make it work for me.

3

The main goal is to convert date to this format: “year, weeknum, first day of week — last day of week”. Note that week start at Monday, so my formula was:

CONCAT(year(Date), ‘, ‘, right_TEXT(week(Date)+100,2), ‘, ‘,
format_datetime(’%d %b’, DATETIME_SUB(Date, interval (if(weekday(Date)=0,7,weekday(Date))-1) day))
,’ — ‘,
format_datetime(’%d %b’, DATETIME_add(Date, interval (7-if(weekday(Date)=0,7,weekday(Date))) day)))

The date ‘9/30/2025’ was converted to ‘2025, 40, 29 Sept — 5 Oct’. Maybe someone will come up with the better solution.