Search code examples
datetimeconverterslooker-studioiso8601

Convert Text(Date in ISO 8601 format) to DateTime - Data Studio

I need to convert a text that is in ISO 8601 Date format, to DateTime in DataStudio. When the text comes with the hour and timezone parts having non-zero values, it works normally. enter image description here

Code to convert: DATETIME_ADD(PARSE_DATETIME("%FT%R:%E3S",LEFT_TEXT(CreatedOn,23)), INTERVAL 11-CAST(SUBSTR(CreatedOn,24,3) as INT64) HOUR)

But when the hour and timezone parts come as zero, the conversion becomes null: enter image description here

Code to convert: DATETIME_ADD(PARSE_DATETIME("%FT%R:%E3S",LEFT_TEXT(BGInvoiceDate,23)), INTERVAL 11-CAST(SUBSTR(BGInvoiceDate,24,3) as INT64) HOUR)

Solution

  • you can try:

    PARSE_DATETIME("%FT%X",LEFT_TEXT(BGInvoiceDate,19))

    enter image description here