Converting a YYYY-MM-DD to text (ex: July, 17, 2016) in SQL (PostgreSQL database)

Question

SELECT date, percent FROM errorpercent WHERE percent > 1;

   date    |        percent         
2016-07-17 | 2.26268624680272595600

I would like 2016-07-17 (as well as the potentially many other rows) to be converted to the format of July 17, 2016.

Answer 1

If you are using Oracle DBMS, then simpler way is to use to_Char function, for example then this query will be :

select to_char(date,'Month DD, YYYY') as new_date,
       percent 
  FROM errorpercent 
 WHERE percent > 1;

Answer 2

Nearly all databases have their own date formatting functions.

See the many questions tagged sql, date-formatting, date, format to learn more. You can also search stackoverflow for:<your database> date format

Following you will find solutions for the other 4 most commonly used SQL databases (besides Oracle oracle already answered):

IBM DB2 db2

.. then you can use VARCHAR_FORMAT scalar function. This works on LUW (Linux/Unix/Windows) as well as iSeries (formerly AS400) systems. Your SQL query would then look like this:

SELECT
  VARCHAR_FORMAT(date,'Month DD, YYYY') as formatted_date
, percent  
FROM
  errorpercent
WHERE
  percent > 1

See also:

• similar question DB2 Date format
• IBM's official DB2 Documentation

MySQL mysql

.. then you can use DATE_FORMAT scalar function. Your SQL query would then look like this:

SELECT
  DATE_FORMAT(date, '%M %D, %Y') as formatted_date
, percent  
FROM
  errorpercent
WHERE
  percent > 1

See also:

• similar question MySQL date format
• official MySQL Documentation

Microsoft SQL Server sql-server

.. then you can use CONVERT scalar function. Your SQL query would then look like this:

SELECT
  CONVERT(VARCHAR, date, 107) as formatted_date
, percent  
FROM
  errorpercent
WHERE
  percent > 1

See also:

• similar question How to return only the Date from a SQL Server DateTime datatype
• Microsoft's official SQL Server Documentation

PostgreSQL postgresql

.. then you can use TO_CHAR scalar function. Your SQL query would then look like this:

SELECT
  TO_CHAR(date, 'Month DD, YYYY') as formatted_date
, percent  
FROM
  errorpercent
WHERE
  percent > 1

See also:

• similar question Postgresql: date format and local language output
• official PostgresSQL Documentation

Answer 3

If the column's data type is date then:

SELECT to_char(date, 'Month, DD, YYYY' ), percent FROM errorpercent WHERE percent > 1;

If it is a string then:

SELECT to_char(to_date(date, 'YYYY-MM-DD'), 'Month, DD, YYYY' ), percent FROM errorpercent WHERE percent > 1;