How can you extract parts of a date (year, month, day) using SQL functions?
NickCrt
Posted by NickCrt
Last Updated: June 15, 2024
SQL date
In SQL, you can extract parts of a date (such as year, month, and day) using various functions that are specific to the database management system (DBMS) you are using. Below are examples for some common SQL databases:
1. MySQL
In MySQL, you use the YEAR(), MONTH(), and DAY() functions: 
SELECT 
    YEAR(your_date_column) AS year,
    MONTH(your_date_column) AS month,
    DAY(your_date_column) AS day
FROM your_table;
2. PostgreSQL
In PostgreSQL, you can use the EXTRACT() function: 
SELECT 
    EXTRACT(YEAR FROM your_date_column) AS year,
    EXTRACT(MONTH FROM your_date_column) AS month,
    EXTRACT(DAY FROM your_date_column) AS day
FROM your_table;
3. SQL Server
In SQL Server, you can utilize the YEAR(), MONTH(), and DAY() functions similar to MySQL: 
SELECT 
    YEAR(your_date_column) AS year,
    MONTH(your_date_column) AS month,
    DAY(your_date_column) AS day
FROM your_table;
4. Oracle
In Oracle, you use the EXTRACT() function: 
SELECT 
    EXTRACT(YEAR FROM your_date_column) AS year,
    EXTRACT(MONTH FROM your_date_column) AS month,
    EXTRACT(DAY FROM your_date_column) AS day
FROM your_table;
5. SQLite
In SQLite, you can use the strftime() function: 
SELECT 
    strftime('%Y', your_date_column) AS year,
    strftime('%m', your_date_column) AS month,
    strftime('%d', your_date_column) AS day
FROM your_table;
Conclusion
Be sure to replace your_date_column and your_table with your actual column and table names. The syntax might vary slightly between different SQL dialects, but the functions generally perform the same task of extracting year, month, and day from date values.
Default Image