AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

Data Studio
1.9K members online now
1.9K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Get substring of date field

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi guys, I am trying to get a working date field from my source file. Date in my source is this format:

2017-03-01 23:00:00+0000

 

To get this working in data studio date needs to have this format: 

2017-03-01

 

So what i need is a new field with a substring of the original date field. I tried this: ("day" is my date field in source)

SUBSTR(day, 1, 10)

 

Unfortunately this seems not to work. Any ideas?

Get substring of date field

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

Hi @Kai H,

 

How about making a new calculated field using the REGEX_EXTRACT() function and the following regex:

\b(?:[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])\b

 

This will out out just the date part '2017-03-01' of your field, which if you then format as a date, should work perfectly.

 

Hope that helps,

Ben

Get substring of date field

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hi Ben, thanks for your answer. Can you please tell me where I need to put my field reference "day" in this formula? 

Best regards, 

Kai

Get substring of date field

Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

Hi Kai,

 

The syntax is REGEXP_EXTRACT(field_expression, regexp) so the below should work for you:

 

REGEXP_EXTRACT(day, '\b(?:[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9])\b')

 

Smiley Happy

 

Ben

Get substring of date field

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Hi Ben, thanks again. Unfortunately this does not work. I do not get any data to the tables after using the new calculated date field for time range and dimension. Please find below my source table. Does it work for you?

Best regards

 

day amount count
2017-04-01 23:00:00+0000 100,50 10

Get substring of date field

Explorer ✭ ✭ ✭
# 6
Explorer ✭ ✭ ✭

Hi Kai,

 

Are you using the time in your data at all or is it redundant? Are the metrics dependant on the time, as far as you know? What data source are you using? Would it be easier to add a new data at the data level, on the same rows as your data?

 

Ben

Get substring of date field

Visitor ✭ ✭ ✭
# 7
Visitor ✭ ✭ ✭

Hi Ben, time is completely redundant. Don't need the time at all, just the dates. Data source is Google Spreadsheets and doing the date thing in the sheet wouldn't be a problem (=LEFT(A2;10)). I would like to avoid doing it in the sheets. 

Get substring of date field

Explorer ✭ ✭ ✭
# 8
Explorer ✭ ✭ ✭

Hi Kai,

 

Let's try REGEX_REPLACE() instead, this will remove/ hide the time stamp and hopefully keep the data in context:

 

REGEXP_REPLACE(day, ' ([01]?[0-9]|2[0-3]):[0-5][0-9]:[0-9][0-9]\+[0-9][0-9][0-9][0-9]', " ")

 

Ben

Get substring of date field

Visitor ✭ ✭ ✭
# 9
Visitor ✭ ✭ ✭

Hi Ben, I get the following error when trying to save this: 

"The formula could not be parsed."