How to emulate to_char() function in BigQuery

The to_char() function is a super useful way to turn a timestamp, date, or number into a nicely formatted string. You can use it to extract specific date parts or prettify numeric values with leading zeroes, thousands separators, and more.

But what if you’re working in BigQuery? There’s no to_char() function — but you can get the same results using:

• FORMAT_TIMESTAMP() for timestamps
• FORMAT_DATE() for dates
• FORMAT() for numbers

Let’s break it down and see how it works.

Formatting timestamps with FORMAT_TIMESTAMP()

When using to_char() function for a standard timestamp formatting, you can write:

SELECT to_char(created_at, 'YYYY-MM-DD HH24:MI:SS')
FROM users

In BigQuery, we can emulate this using FORMAT_TIMESTAMP():

SELECT FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', created_at)
FROM users

The two formats are almost identical, with just a few small differences in formatting tokens.

Here’s a list of all formatting tokens for the FORMAT_TIMESTAMP() function in BigQuery:

Here’s a nice example:

SELECT FORMAT_TIMESTAMP('%A, %B %d, %Y at %I:%M %p', TIMESTAMP '2024-12-25 18:45:00')
-- Returns: 'Wednesday, December 25, 2024 at 06:45 PM'

Formatting dates with FORMAT_DATE()

If your column is a plain DATE instead of TIMESTAMP, you can use the FORMAT_DATE() function in BigQuery.

When using to_char() function:

SELECT to_char(signup_date, 'Month DD, YYYY')
FROM users

In BigQuery:

SELECT FORMAT_DATE('%B %d, %Y', signup_date)
FROM users

You can use all the same date formatting tokens with FORMAT_DATE() that you use with FORMAT_TIMESTAMP(). The only difference is that you can’t use time-related tokens (like hours, minutes, or seconds) since we’re working with dates only.

Formatting numbers with FORMAT()

When using to_char() function we’d format a price number like so:

SELECT to_char(price, 'FM999,999.00')
FROM products

In BigQuery, we’d use the FORMAT() function for this purpose:

SELECT FORMAT('%,.2f', price)
FROM products

For example, 12345.6 becomes '12,345.60'.

The FORMAT() function has a lot of formatting tokens:

Let’s try a simple BigQuery-friendly version:

SELECT FORMAT('%.0f', price)
FROM products

This will round prices to a whole number without commas.

Summary

BigQuery’s functions might look different, but they give you the same flexibility when it comes to string formatting.