
Who is not familiar with this situation? What was that thing again with the correct timestamp format? Just google it and next time it will end so again. Here are some of the use cases, that I often come in touch with and which I wanted to write down, in the hope not to have to google them again.
Current Date Time
Let’s start with a very easy but useful one. Get the current time – you might need it for comparison e.g. within analytics or for technical data transformations:
SELECT CURRENT_DATETIME() as now;
This function supports the optional timezone parameter. See Timezone definitions for instructions on how to set a timezone [1].

EXTRACT
Return a value corresponding to the specified part from a provided _datetimeexpression. For example, here I extracted only the year. I often use this logic for constraints in analyses or for data transformations, where I convert date formats and store them in new tables in a different format.
SELECT EXTRACT(YEAR FROM DATETIME(2021, 07, 02, 15, 45, 00)) as year;

DATETIME Difference
Calculates the difference between two dates. If the first DATETIME is before the second, the output is negative [1].
Select DATETIME_DIFF(DATETIME"2021–03–03", DATETIME"2021–04–04", Day)as difference;
This results in -32.
Formatting DATETIMES
Format a DATETIME object according to the specified _formatstring. Similar to the other examples, one likes to use this formatting option in the data transformation process.
SELECT
FORMAT_DATETIME("%c", DATETIME "2008–12–25 15:30:00")
AS formatted;
Here, for example with %c, the whole date and time representation are output [2].

PARSE DATETIME
With _PARSEDATETIME you can do the opposite of the example mentioned above. It converts the string representation of a DATETIME object into a DATETIME object.
SELECT PARSE_DATETIME('%Y-%m-%d %H:%M:%S', '2021–03–03 13:55:55') AS datetime;

Always useful when other sources for example display the date as string and you want to display it in Bigquery as standardized DATETIME.
LAST DAY
Returns the last day from a DATETIME expression containing the date. This is often used to return the last day of the month. You have the option to specify the date part for which the last day should be returned.
SELECT LAST_DAY(DATETIME '2008–11–25', MONTH) AS last_day
If this parameter is not used, the default value is MONTH. Especially in the area of evaluations, for example for a financial month statement interesting and often used.

Summary
Since I often find myself googling date formats within BigQuery because I forgot what the correct Sql expression was, I have provided you with my cheatsheet on the subject. These are the most common use cases in my opinion – feel free to let me know if I forgot anything.
Sources and Further Readings
[1] https://cloud.google.com/bigquery/docs/reference/standard-sql/datetime_functions?hl=de