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(response_time, 'NANOS', '%Y%m%d')
TODATE(create_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

  • YYYYMMDD (canonical Data Studio date): "%Y%m%d"
  • Date Hour: "%Y%m%d%H"
  • Year Week: "%Y%W"
  • Year Month: "%Y%m"

Notes

TODATE returns compatibility mode Date data types.

Was this helpful?
How can we improve it?

Need more help?

Sign in for additional support options to quickly solve your issue