SQL Convert Date to String Functions

Summary: in this tutorial, you will learn various functions to convert a date to a string in SQL.

Convert date to string using CAST() function

To convert a date to a string, you use the CAST() function as follows:

CAST(date AS string)
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• The date can be a literal or an expression that evaluates to a DATE value.
• The string can be any character string data type such as VARCHAR or TEXT.

The CAST() function returns a string that represents the date.

The following statement returns the current date and time as a date and as a string:

SELECT CURRENT_TIMESTAMP 'date',
   CAST(CURRENT_TIMESTAMP AS VARCHAR) 'date as a string';
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

Even though CAST() is a standard-SQL function, not so many database systems support it.

Convert date to string using TO_CHAR() function

The DB2, Oracle, MySQL and PostgreSQL provide a function named TO_CHAR() that has a similar feature to the CAST function. You can use the TO_CHAR() function to format a date as a string.

The following illustrates the syntax of the TO_CHAR() function:

TO_CHAR(value, format);
Code language: SQL (Structured Query Language) (sql)

The following example uses the TO_CHAR() function to format the current date using the YYYY-MM-DD format in Oracle:

SELECT 
    TO_CHAR(SYSDATE, 'YYYY-MM-DD') 
FROM 
    dual;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

2018-07-21
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the CAST() and TO_CHAR() functions to convert a date to a string in SQL.