Back to list PostgreSQL How to Extract the Week Number from a Date in PostgreSQL Database: PostgreSQL Operators:EXTRACT(), DATE_PART(), WEEK Problem: You want to get the week number of a date or timestamp value in a PostgreSQL database. Example: Our database has a table named children with data in the columns id, first_name, last_name, and birth_date. idfirst_namelast_namebirth_date 1AngelaMichelin2018-01-01 2MartinJackson2002-07-26 3JustinClark2010-12-26 4FrankBarker2008-06-08 For each child, let’s get their first name, last name, and the week number of their birth date. Solution 1: We’ll use the DATE_PART() function. Here’s the query you would write: SELECT first_name, last_name, DATE_PART('week',birth_date) AS birth_week_number FROM children; Here’s the result of the query: first_namelast_namebirth_week_number AngelaMichelin1 MartinJackson30 JustinClark51 FrankBarker23 Discussion: Use the DATE_PART() function to retrieve the week number from a date in a PostgreSQL database. This function takes two arguments. The first argument is the date part to retrieve; we use ‘week’, which returns the week number (e.g. “1” for the first week in January, the first week of the year). You can use another date part, like day, year, month, hour, minute, doy (the day’s number in the year), etc. You can learn more about date parts in the PostgreSQL documentation. The second argument is a column name of the date/timestamp/timestamptz data type or an expression returning one of these types. (In our example, it is a column of the date data type.) When used with the ‘week’ date part, the function DATE_PART() returns the week number as an integer. In our example, the week number for the given child’s birthdate is displayed; we named this column birth_week_number. Martin Jackson was born on 2002-07-26, so the function returns ‘30’ as the week number of his birthdate. DATE_PART() is not only one way to get the week number. Another option is to use the EXTRACT() function. Solution 2: We’ll use the EXTRACT() function. Here’s the query you would write: SELECT first_name, last_name, EXTRACT('week' FROM birth_date) AS birth_week_number FROM children; This function is similar to DATE_PART(). The difference is in the syntax: instead of a comma between the date part name and the column/expression, there is the keyword FROM. Also, the EXTRACT() function is SQL standard. The result is the same for both functions. Recommended courses: SQL Basics Standard SQL Functions SQL Practice Set Recommended articles: 18 Useful Important SQL Functions to Learn ASAP Performing Calculations on Date- and Time-Related Values How Often Employees Are Running Late for Work: SQL Datetime and Interval SQL Arithmetic See also: How to Group by Month in PostgreSQL How to Find the Interval Between Two Dates in PostgreSQL How to Get the Day of the Year from a Date in PostgreSQL Tags: PostgreSQL Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.