FORMAT in JS

Applies to

• SQL Server

• Azure Synapse Analytics

Description

There are different functions to format date and integer values in JavaScript. Unfortunately, these functionalities are not integrated into one method.

DateTime values

Syntax

Intl.DateTimeFormat( format ).format( value )

Arguments

locales (Optional): String expression of the format to apply.

options (Optional): Object with different supported properties for formats of numeric expressions (JavaScript NumberFormat function Documentation).

value: Numeric expression to format.

Return Type

String.

Numeric values

Syntax

Intl.NumberFormat( [locales [, options]] ).format( value )

Arguments

locales (Optional): String expression of the format to apply.

options (Optional): Object with different supported properties for formats of numeric expressions (JavaScript NumberFormat function Documentation).

value: Numeric expression to format.

Return Type

String.

Examples

DateTime

Query

CREATE OR REPLACE FUNCTION format_date(date timestamp, format varchar)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return new Intl.DateTimeFormat( FORMAT ).format( DATE );
$$;
SELECT FORMAT_DATE(TO_DATE('2022-01-24'), 'en-gb') GREAT_BRITAIN;

Result

  GREAT_BRITAIN|      
---------------+
     24/01/2022|  

Numeric

Query

CREATE OR REPLACE FUNCTION format_numeric(number float, locales varchar, options variant)
  RETURNS string
  LANGUAGE JAVASCRIPT
AS
$$
  return new Intl.NumberFormat( LOCALES , OPTIONS ).format( NUMBER );
$$;
SELECT FORMAT_NUMERIC(244900.25, 'de-DE', PARSE_JSON('{ style: "currency", currency: "CRC" }')) CURRENCY;

Result

       CURRENCY|      
---------------+
 244.900,25 CRC|  

Known Issues

No issues were found.

Related EWIs

No related EWIs.