Use-case: I’m interested in aggregating my data by week. I could use the weekofyear() function to get week number, but I would rather to show display more user friendly such as the start date of each week.
Solution: Use trunc(timestamp, “D”) function to truncate each date down to the starting day of that week.
Step 1: Enter your custom expression.
select trunc([order_date]), “D”)
OR if your date/timestamp is a string type:
select trunc(cast([order_date_string] as timestamp), “D”)
Result: Your dates are now bucketed by week, and showing the start date for each week
Step 2: Prettify your dates
You can trim timestamps down to simple date values using the to_date() function:
You can also use display format to modify the timestamp to look like a more user friendly date: