FORMAT_DATE

Format_date function

Description

Formats a DATE value according to a specified format string.

For more information, please refer to FORMAT_DATE function.

Grammar Syntax

FORMAT_DATE(format_string, date_expr)

Sample Source

Input Code:

BigQuery

IN -> BigQuery_01.sql

CREATE TABLE TEST_DATE (col1 DATE);
SELECT FORMAT_DATE('%Y', col1);

Output Code:

Snowflake

OUT -> BigQuery_01.sql

CREATE TABLE TEST_DATE (col1 DATE);
SELECT
  TO_CHAR(col1, 'YYYY')
FROM
  TEST_DATE;

BigQuery Formats Equivalents

BigQuery	Snowflake
%A	PUBLIC.DAYNAME_LONG_UDF(date_expr) Note: Generate UDF in conversion for support.
%a	DY
%B	MMMM
%b	MON
%C	PUBLIC.CENTURY_UDF(date_expr) Note: Generate UDF in conversion for support.
%c	DY MON DD HH24:MI:SS YYYY
%D	MM/DD/YY
%d	DD
%e	DD
%F	YYYY-MM-DD
%G	YEAROFWEEKISO(date_expr)
%g	PUBLIC.ISO_YEAR_PART_UDF(date_expr, 2) Note: Generate UDF in conversion for support.
%H	HH24
%h	MON
%I	HH12
%J	PUBLIC.DAY_OF_YEAR_ISO_UDF(date_expr) Note: Generate UDF in conversion for support.
%j	DAYOFYEAR(date_expr)
%k	HH24
%l	HH12
%M	MI
%m	MM
%n	Not equivalent format
%P	pm
%p	AM
%Q	QUARTER(date_expr)
%R	HH24:MI
%S	SS
%s	Not equivalent format
%T	HH24:MI:SS
%t	Not equivalent format
%U	WEEK(date_expr)
%u	DAYOFWEEKISO(date_expr)
%V	WEEKISO(date_expr)
%W	WEEK(date_expr) Note: Unlike BigQuery, Snowflake results are dictated by the values set for the WEEK_OF_YEAR_POLICY and/or WEEK_START session parameters. So, results could differ from BigQuery based on those parameters.
%w	DAYOFWEEK(date_expr) Note: Unlike BigQuery, Snowflake results are dictated by the values set for the WEEK_OF_YEAR_POLICY and/or WEEK_START session parameters. So, results could differ from BigQuery based on those parameters.
%X	HH24:MI:SS
%x	MM/DD/YY
%Y	YYYY
%y	YY
%Z	Not equivalent format
%z	Not equivalent format
%Ez	Not equivalent format
%E<number>S	Not equivalent format
%E*S	Not equivalent format
%EY4	YYYY

In BigQuery, the format related to time is not applied when the type is DATE, but Snowflake applies the format with values in zero for HH:MI:SS usages.

For more information, please refer to BigQuery DateTime formats.