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

Date Time Field DataStudio unable to identify datsource DateTime Field

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

I have a column in my Table which displays Date and Time of Sales transaction in "YYYY-MM-DD HH:MMSmiley FrustratedS" format. 

DataStudio is not able to identify this field. Basically it seems it can only identify few set formats , any method to create a customised Date Time format.

Thanks

Re: Date Time Field DataStudio unable to identify datsource DateTime Field

Explorer ✭ ✭ ✭
# 2
Explorer ✭ ✭ ✭

I was having this same issue with Bigquery tables that use Google Sheets as a federated data source. I'm not sure what type of data source you are using, but I was able to make Data Studio recognize the date column by changing the format in Sheets from YYYY-MM-DD to YYYYMMDD then adding the Date column in Bigquery as a String rather than a Date or Timestamp. 

I should also point out that changing the date format with a custom dimension in Data Studio instead of changing the format in Sheets did not work even though that method has worked for other types of data sources. I also tried using many other combinations of Date formats in Sheets, Bigquery and Data Studio but the YYYMMDD format in Sheets with String format in Bigquery was the only combo that worked properly in Data Studio. 

If you are using a different type of data source, the solution to your problem may be different, but reformatting the date in the original data source or within Data Studio will probably make your dates work. 

Re: Date Time Field DataStudio unable to identify datsource DateTime Field

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭
My data source is MySQL which has a default date/timestamp format as
YYYY-MM-DD HH:MMSmiley FrustratedS.
The only other thing I can do there is to make that a complete number like
this YYYYMMDDHHMMSS.

DataStudio identifies former as string. So no date range kind of operations
are possible.
For later I am not sure, Do you think latter can help ?

Date Time Field DataStudio unable to identify datsource DateTime Field

[ Edited ]
Explorer ✭ ✭ ✭
# 4
Explorer ✭ ✭ ✭

Have you tried changing the date format in Data Studio by going to custom dimensions > find date column > change format from text to Date YYYYMMDD?

You could also try creating a new custom dimension with a REPLACE or REGEXP_REPLACE or LEFT formula that leaves you with just the YYYYMMDD part. 

Date Time Field DataStudio unable to identify datsource DateTime Field

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Create a new field and use this formula TODATE(created_at,'%Y-%m-%d %H:%M:%S', '%Y%m%d')

One problem I had was the table name was order and thats a reserved keyword. Google didn't apply backticks so everything failed.