Cloud Spanner FORMAT_DATE Function and Example

You can use the FORMAT_DATE function to format the string containing a date expression. It is one of the highly used functions to format date. This function is equivalent to to_char and to_date function present in other relational databases. In this article, we will check cloud Spanner FORMAT_DATE function, syntax and some example

Cloud Spanner FORMAT_DATE Function

The format_date function is used to date expression to required format.

Cloud Spanner FORMAT_DATE function formats the date expression containing date according to the specified date format string.

Cloud Spanner FORMAT_DATE Function Syntax

Following is the FORMAT-DATE syntax.

FORMAT_DATE(format_string, date_expr)

Where, date_expr is date expression and format_string is the date format string.

Format Elements for DATE

The FORMAT_DATE functions that use format strings support the following elements

Format Element	Description
%A	The full weekday name.
%a	The abbreviated weekday name.
%B	The full month name.
%b or %h	The abbreviated month name.
%C	It is the century as a decimal number (00-99). A year divided by 100 and truncated to an integer .
%D	The date in the format %m/%d/%y.
%d	The day of the month as a decimal number (01-31).
%e	The day of month as a decimal number (1-31); single digits are preceded by a space.
%F	The date in the format %Y-%m-%d.
%G	The ISO 8601 year with century as a decimal number.
%g	The ISO 8601 year without century as a decimal number (00-99).
%j	The day of the year as a decimal number (001-366).
%m	The month as a decimal number (01-12).
%n	A newline character.
%t	A tab character.
%U	The week number of the year as a decimal number (00-53). . Sunday as the first day of the week.
%u	The weekday (Monday as the first day of the week) as a decimal number (1-7).
%V	The ISO 8601 week number of the year (Monday as the first day of the week) as a decimal number (01-53).
%W	The week number of the year as a decimal number (00-53). Monday as the first day of the week
%w	The weekday as a decimal number (0-6). Sunday as the first day of the week.
%x	The date representation in MM/DD/YY format.
%Y	The year with century as a decimal number.
%y	The year without century as a decimal number (00-99), with an optional leading zero.
%E4Y	Four-character years (0001 … 9999).

Cloud Spanner FORMAT_DATE Function Examples

Following are the some of the common example FORMAT_DATE functions

Cloud Spanner Convert Date to DD-MM-YYYY Format

select FORMAT_DATE('%d-%m-%Y', current_date) as date;
date
13-07-2020

Google Cloud Spanner Convert Date to DD/MM/YYYY Format

select FORMAT_DATE('%d/%m/%Y', current_date) as date;
date
13/07/2020

Cloud Spanner Convert Date to DD/MM/YY Format

select  FORMAT_DATE('%x', current_date) as date;
date
07/13/20

Google Cloud Spanner Convert Date to DD-MM-YY Format

select FORMAT_DATE('%d-%m-%y', current_date) as date;
date
13-07-20

Cloud Spanner Convert Date to MON-YYYY Format

SELECT FORMAT_DATE("%b-%Y", current_date) AS formatted;
formatted
Jul-2020

Google Cloud Spanner Convert Date to DD-MON-YYYY Format

SELECT FORMAT_DATE("%d-%b-%Y", current_date) AS formatted;
formatted
13-Jul-2020

Cloud Spanner Convert Date to YYYY-MM-DD Format

SELECT FORMAT_DATE("%F", current_date) AS formatted;
formatted
2020-07-13

Hope this helps 🙂