Notification

This Help Center is moving to Cloud. Learn more about the migration.

TODATE

Returns a formatted compatibility mode Date.

This function only supports compatibility mode dates.

We recommend upgrading any older date fields in your data sources to the new Date or Date & Time types. After upgrading:

  • You can use the FORMAT_DATETIME function to format a date as text.
  • You can use the PARSE_DATE and PARSE_DATETIME functions to convert text into Date and Date & Time fields.

Learn more.

You can use TODATE in 2 ways:

  • Create a compatibility mode date from Text fields.
  • Change the display format and granularity of a compatibility mode date.

Sample usage

TODATE(Text_Date,"%Y/%m/%d","%Y-%m-%d")
 
TODATE(Text_Date,"%Y/%m/%d","%Y-%m-%d")
 
TODATE(Response_Time, 'MILLIS', '%Y%m%d')
 
TODATE(Created_Time, 'DECIMAL_DATE', '%Y')

Syntax

TODATE(field_expression, input_date_format, output_date_format)

Input formats

input_date_format is optional if field_expression is correctly configured as a semantic date.

TODATE accepts the following input format strings.

  • 'BASIC': %Y/%m/%d-%H:%M:%S

  • 'DEFAULT_DASH': %Y-%m-%d [%H:%M:%S]

  • 'DEFAULT_SLASH': %Y/%m/%d [%H:%M:%S]

  • 'DEFAULT_DECIMAL': %Y%m%d [%H:%M:%S

  • 'RFC_1123': for example, Sat, 24 May 2008 20:09:47 GMT

  • 'RFC_3339': for example, 2008-05-24T20:09:47Z

  • 'SECONDS': seconds since epoch

  • 'MILLIS': milliseconds since Epoch

  • 'MICROS': microseconds since Epoch

  • 'NANOS': nanoseconds since Epoch

  • 'JULIAN_DATE': days since Epoch

  • 'DECIMAL_DATE': same as 'DEFAULT_DECIMAL'

  • Any valid strptime format. Optional if the value is already a datetime field.

Output formats

TODATE accepts the following output format strings:

  • "%Y" - year
  • "%m" - month
  • "%d" - day
  • "%W" - yearweek number
  • "%w" - day of week
  • "%H" - hour
  • "%M" - minute

Examples

Example formula Input Output
TODATE(Text_Date,"%Y/%m/%d","%Y-%m-%d") 2024/05/09 2024-05-09
TODATE(Response_Time, 'MILLIS', '%Y%m%d') 1715294861000 20240509
TODATE(Created_time, 'DECIMAL_DATE', '%Y') 20240509 [11:34:12] 2024

Notes

TODATE returns compatibility mode Date data types.

Was this helpful?

How can we improve it?
true
What's new in Looker Studio?

Learn about new features and recent changes. Looker Studio release notes live on Google Cloud.

Search
Clear search
Close search
Google apps
Main menu
8033503961389717265
true
Search Help Center
true
true
true
true
true
102097
false
false