如何在SQL中从日期列提取季度？日期列数据示例为23-3-2021

Got it, let's work through this. Since your date values are stored as strings in dd-mm-yyyy format (e.g., 23-3-2021), the key first step is converting that string into a proper date data type—this lets your SQL engine reliably calculate the quarter. Below are tailored solutions for the most common database systems:

MySQL/MariaDB #

Use STR_TO_DATE() to parse the string into a date, then QUARTER() to get the 1-4 quarter number:

SELECT
    your_date_column,
    QUARTER(STR_TO_DATE(your_date_column, '%d-%m-%Y')) AS calendar_quarter
FROM
    your_table;

• %d = day (1 or 2 digits), %m = month (1 or 2 digits), %Y = 4-digit year
• The result will be a number between 1 (Jan-Mar) and 4 (Oct-Dec)

PostgreSQL #

Use TO_DATE() to convert the string, then either EXTRACT() for a numeric quarter or TO_CHAR() for a text label like "Q1":

SELECT
    your_date_column,
    EXTRACT(QUARTER FROM TO_DATE(your_date_column, 'DD-MM-YYYY')) AS quarter_number,
    TO_CHAR(TO_DATE(your_date_column, 'DD-MM-YYYY'), 'Q') AS quarter_label
FROM
    your_table;

SQL Server #

Use CONVERT() with format code 105 (which maps to dd-mm-yyyy), then DATEPART() to fetch the quarter:

SELECT
    your_date_column,
    DATEPART(QUARTER, CONVERT(DATE, your_date_column, 105)) AS quarter
FROM
    your_table;

If you're using SQL Server 2012+, TRY_CONVERT() is safer—it returns NULL instead of an error if a string can't be parsed as a valid date.

Oracle #

Use TO_DATE() to parse the string, then choose between EXTRACT() for a numeric value or TO_CHAR() for a text quarter:

SELECT
    your_date_column,
    EXTRACT(QUARTER FROM TO_DATE(your_date_column, 'DD-MM-YYYY')) AS quarter_number,
    TO_CHAR(TO_DATE(your_date_column, 'DD-MM-YYYY'), 'Q') AS quarter_text
FROM
    your_table;

Quick Note #

If your column is already a date type (not a string) but just displays as dd-mm-yyyy, you can skip the conversion step and directly use the quarter function for your database. Always verify a few rows after running the query to make sure the conversion worked correctly—bad date strings (like 32-13-2021) will cause errors in most cases unless you use a "try" function (like TRY_CONVERT or TRY_CAST).

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