TO_TEXT

Converts a value, whether it's a datetime or any other data type, to a text format

Updated over a week ago

Description

The TO_TEXT() function is a versatile function used to convert various data types, including datetimes, into text format. This is beneficial when you need to display or manipulate data in text form, such as custom date formatting or converting non-text values to text.

Syntax

TO_TEXT(value), TO_TEXT(datetime, β€œ[format]”)

Variables

  1. value: The value you want to convert to text. This can be a datetime, number, or any other compatible data type.

  2. format: (Optional) A parameter specifying the desired text format for the datetimes or the number. If not provided, a default format is used.

Numeric format options:

  • TO_TEXT(numeric, "05.3f") means format the numeric value into 3 decimals, at least 5 characters in length, with 0 paddings if needed.

Date Specifiers

Symbol

Meaning

%Y

4-digit year (e.g., 2023)

%m

2-digit month (01-12)

%d

2-digit day of the month (01-31)

%H

2-digit hour in 24-hour format (00-23)

%I

2-digit hour in 12-hour format (01-12)

%M

2-digit minute (00-59)

%S

2-digit second (00-59)

%p

AM/PM indicator (AM or PM)

%Z

Time zone offset in the format "+- 0000 hours" (e.g., "+0300 hours" or "-0500 hours")

Example

TO_TEXT(`TO_DATE(DOB)`, '%d/%m/%Y')

Name

DOB

TO_TEXT

Giacomo

2/15/1983

15/02/1983

Kameko

4/19/1969

19/04/2069

Anne

2/06/2012

6/2/12

Orlando

4/28/1988

28/04/1988

Nasim

3/02/2007

2/3/07

Format Examples using Date Specifiers

Format String

Description

%Y-%m-%d

yyyy-MM-dd

%Y/%m/%d

yyyy/MM/dd

%d-%m-%Y

dd-MM-yyyy

%d/%m/%Y

dd/MM/yyyy

%m-%d-%Y

MM-dd-yyyy

%m/%d/%Y

MM/dd/yyyy

%Y-%m

yyyy-MM

%Y/%m

yyyy/MM

%m-%d

MM-dd

%m/%d

MM/dd

%d-%m

dd-MM

%d/%m

dd/MM

%Y-%m-%d %H:%M:%S

yyyy-MM-dd H:mm:ss

%Y-%m-%d %I:%M:%S %p

yyyy-MM-dd hh:mm:ss a

%Y-%m-%d %H:%M:%S %Z

yyyy-MM-dd H:mm:ss Z

%Y-%m-%d %I:%M:%S %p %Z

yyyy-MM-dd hh:mm:ss a Z

%m/%d/%Y %H:%M:%S

MM/dd/yyyy H:mm:ss

%m/%d/%Y %I:%M:%S %p

MM/dd/yyyy hh:mm:ss a

%m/%d/%Y %H:%M:%S %Z

MM/dd/yyyy H:mm:ss Z

%m/%d/%Y %I:%M:%S %p %Z

MM/dd/yyyy hh:mm:ss a Z

%d/%m/%Y %H:%M:%S

dd/MM/yyyy H:mm:ss

%d/%m/%Y %I:%M:%S %p

dd/MM/yyyy hh:mm:ss a

%d/%m/%Y %H:%M:%S %Z

dd/MM/yyyy H:mm:ss Z

%d/%m/%Y %I:%M:%S %p %Z

dd/MM/yyyy hh:mm:ss a Z

Did this answer your question?