To extract the Month year from a date in PostgreSQL, you can use the DATE_PART() function with the ‘month’ and ‘year’ unit. To Extract Month year from date we will also be using Extract() Function
Syntax for DATE_PART() Function in PostgreSQL:
DATE_PART(unit, source)
unit: Specifies the part of the date or timestamp to extract (e.g., ‘year’, ‘month’, ‘day’, ‘hour’, ‘minute’, ‘second’, etc.).
source: The date or timestamp from which to extract the specified part
In Our case. The values of the unit must be “month” and “year”.
Extract Month Year from Date in postgresql – DATE_PART() function :
In order to Extract Month Year from date in postgresql we will be using DATE_PART() function.
In the below Example we will be passing month & year as “unit” argument and date as “source” argument in order to extract month year from Date or datetime
SELECT concat(DATE_PART('month', '2024-02-24'::timestamp) ,'-', DATE_PART('year', '2024-02-24'::timestamp)) AS month_year;
Output:
Get Month Year from Date in PostgreSQL – Extract() function :
In order to Extract Month Year from date in postgresql we will also be using EXTRACT() function.
In the below Example we will be passing month & year as “unit” argument and date as “source” argument in order to extract month year from Date or datetime
SELECT concat(Extract (Month from '2024-02-24'::timestamp) ,'-', Extract (Year from '2024-02-24'::timestamp)) AS month_year;
Output:
Get Month Year from Date in postgresql table using DATE_PART(): Method 1
We will be using below Student_detail table for our example to depict on how to extract month year from date in postgresql
Student_detail:
In the above table we will be using DATE_PART() Function, which will take “month” and column named “birthdaytime” as argument. Another Function, which will take “Year” and column named “birthdaytime” as argument. And concatenate both Which will extract Month Year column and store in the new column of postgresql table as shown below
select *, DATE_PART('decade', birthdaytime::timestamp) AS decade_part from Student_detail
so the resultant table will have month_year column
Get Month Year from Date in postgresql table using EXTRACT() function: Method 2
We will be using below Student_detail table for our example to depict on how to extract monthyear from date in postgresql
Student_detail:
In the above table we will be using EXTRACT() Function, which will take “month” and column named “birthdaytime” as argument. Another Function, which will take “Year” and column named “birthdaytime” as argument. And concatenate both Which will extract Month Year column and store in the new column of postgresql table as shown below
SELECT * ,concat(Extract (Month from birthdaytime::timestamp) ,'-', Extract (Year from birthdaytime::timestamp)) AS month_year from student_detail
so the resultant table will have month_year column