SQL中如何将EXTRACT(YEAR_MONTH)输出从201706格式转为2017-06

Got it, let's sort out that date formatting issue! Your current use of EXTRACT(YEAR_MONTH FROM value) returns a numeric string like 201706, but we can adjust this to get the 2017-06 format you need. The approach depends on your SQL dialect—here are solutions for the most common ones:

MySQL/MariaDB #

The simplest way is to use DATE_FORMAT() directly on your date column, which avoids extra string manipulation:

SELECT DATE_FORMAT(value, '%Y-%m') AS value, DATE_FORMAT(value, '%Y-%m') AS text

If you need to work with the result of EXTRACT() specifically, you can split the numeric string and insert a hyphen:

SELECT 
  CONCAT(LEFT(EXTRACT(YEAR_MONTH FROM value), 4), '-', RIGHT(EXTRACT(YEAR_MONTH FROM value), 2)) AS value,
  CONCAT(LEFT(EXTRACT(YEAR_MONTH FROM value), 4), '-', RIGHT(EXTRACT(YEAR_MONTH FROM value), 2)) AS text

PostgreSQL #

Use TO_CHAR() to format the date directly—this is clean and readable:

SELECT TO_CHAR(value, 'YYYY-MM') AS value, TO_CHAR(value, 'YYYY-MM') AS text

If you're set on using EXTRACT(), cast the result to an integer and format it with TO_CHAR():

SELECT 
  TO_CHAR(EXTRACT(YEAR_MONTH FROM value)::integer, 'FM0000-00') AS value,
  TO_CHAR(EXTRACT(YEAR_MONTH FROM value)::integer, 'FM0000-00') AS text

The FM prefix removes any leading whitespace from the formatted string.

SQL Server #

Use FORMAT() for straightforward date formatting (note: this requires SQL Server 2012 or later):

SELECT FORMAT(value, 'yyyy-MM') AS value, FORMAT(value, 'yyyy-MM') AS text

Alternatively, use STUFF() to insert a hyphen into the stringified EXTRACT() result:

SELECT 
  STUFF(CONVERT(varchar(6), EXTRACT(YEAR_MONTH FROM value)), 5, 0, '-') AS value,
  STUFF(CONVERT(varchar(6), EXTRACT(YEAR_MONTH FROM value)), 5, 0, '-') AS text

STUFF() inserts the hyphen at the 5th position of the 6-character string, splitting year and month.

All these methods will return your desired YYYY-MM format instead of the numeric YYYYMM string.

内容的提问来源于stack exchange，提问作者Rhea Lorraine