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
Related Articles
- Google Cloud Spanner String Functions, Syntax, Examples
- Google Cloud Spanner Regular Expression Functions
Hope this helps 🙂