Question

Convert string value (DD/MM/YYYY HH:MM:SS) into timestamp in LookML

  • 19 January 2022
  • 0 replies
  • 125 views

I have date and time field (DD/MM/YYYY HH:MM:SS) stored as a varchar in my database. 

Need to convert this into timestamp/date-time format in order to calculate TAT. 

Have already tried out the following

dimension_group: datetime {type: timetimeframes: [second, minute, hour, date, month, raw]sql: cast(PARSE_DATETIME('%m/%e/%Y %k:%M:%S',${TABLE}."completion time stamp") as timestamp);;}

 

html: {{ rendered_value | date: "%m/%d/%Y %H:%M:%S" }};;

 

 dimension_group: completion_timestamp {    type: time    timeframes: [      raw,      hour,      time,      date,      week,      month,      quarter,      year    ]    sql: CAST(${TABLE}."completion time stamp" AS datetime) ;;    drill_fields: [completion_timestamp_month,completion_timestamp_week,completion_timestamp_date ]  }

 

But it throws a date_format error or failure to convert varchar into timestamp/date

Need help with this. 

 

Thanks in Advance!


This topic has been closed for comments