PostgreSQL String() Function: Get the employee id, first name and hire month of an employee

6. Write a query to get the employee id, first name and hire month of an employee.

Sample Solution:

Code:

-- This SQL query retrieves employee ID, first name, hire date, and extracts the hire month.

SELECT employee_id, -- Selects the employee_id column
       first_name, -- Selects the first_name column
       hire_date, -- Selects the hire_date column
SUBSTR(TO_CHAR(hire_date,'yyyy MM dd'), 6, 2) AS hire_month -- Extracts the hire month from the hire_date column
FROM employees; -- Specifies the table from which to retrieve data, in this case, the employees table

Explanation:

• This SQL query retrieves employee ID, first name, hire date, and extracts the hire month.
• The SELECT statement selects the employee_id, first_name, hire_date, and the extracted hire month.
• The SUBSTR() function is used to extract a substring from the result of the TO_CHAR() function.
• The TO_CHAR() function converts the hire_date column to a string with the specified format 'yyyy MM dd'.
• The SUBSTR() function extracts a substring starting at the 6th character and with a length of 2 characters, which represents the hire month.
• The extracted hire month is labeled as hire_month using the AS keyword.
• The result set will contain the employee ID, first name, hire date, and hire month for each employee in the employees table.

Sample table: employees

Output:

pg_exercises=# SELECT employee_id, first_name, hire_date,
pg_exercises-# SUBSTR(TO_CHAR(hire_date,'yyyy MM dd'), 6, 2) AS hire_month
pg_exercises-# FROM employees;
 employee_id | first_name  | hire_date  | hire_month
-------------+-------------+------------+------------
         103 | Alexander   | 1987-06-20 | 06
         104 | Bruce       | 1987-06-21 | 06
         105 | David       | 1987-06-22 | 06
         106 | Valli       | 1987-06-23 | 06
         107 | Diana       | 1987-06-24 | 06
         114 | Den         | 1987-07-01 | 07
         115 | Alexander   | 1987-07-02 | 07
         116 | Shelli      | 1987-07-03 | 07
         117 | Sigal       | 1987-07-04 | 07
         100 | Steven      | 1987-06-17 | 06
         101 | Neena       | 1987-06-18 | 06
         118 | Guy         | 1987-07-05 | 07
         119 | Karen       | 1987-07-06 | 07
         133 | Jason       | 1987-07-20 | 07
         134 | Michael     | 1987-07-21 | 07
         135 | Ki          | 1987-07-22 | 07
         136 | Hazel       | 1987-07-23 | 07
         138 | Stephen     | 1987-07-25 | 07
         139 | John        | 1987-07-26 | 07
         140 | Joshua      | 1987-07-27 | 07
         141 | Trenna      | 1987-07-28 | 07
...          | ...         | ...
         203 | Susan       | 1987-09-28 | 09
         204 | Hermann     | 1987-09-29 | 09
         205 | Shelley     | 1987-09-30 | 09
         206 | William     | 1987-10-01 | 10
(106 rows)

Practice Online

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous: Write a query to join the text '@example.com' with the email column.
Next: Write a query to get the employee id, email id to discard the last three characters.