Back to SQL Practice: 331 Real Interview Queries
Q7Question 7 of 9

Date, Time, and String Functions in SQL

Answer: Date functions extract parts (YEAR, MONTH), calculate differences (DATEDIFF), and add intervals. String functions join (CONCAT), extract (SUBSTRING), and clean (TRIM, REPLACE) text data.

Key Concepts
  • EXTRACT(YEAR FROM date_col) gets the year. Also MONTH, DAY, HOUR, MINUTE, SECOND. SQL Server uses YEAR(date_col) instead.
  • DATEDIFF calculates the difference between two dates. Syntax varies: PostgreSQL uses date1 - date2, MySQL uses DATEDIFF(date1, date2), SQL Server uses DATEDIFF(unit, start, end).
  • DATE_ADD/DATEADD adds an interval to a date. PostgreSQL: date + INTERVAL '30 days'. MySQL: DATE_ADD(date, INTERVAL 30 DAY).
  • CONCAT joins strings. Use CONCAT(first, ' ', last) or the || operator (PostgreSQL) or + (SQL Server).
  • SUBSTRING(col, start, length) extracts a portion. Positions are 1-based in SQL, not 0-based like most programming languages.
  • TRIM removes leading/trailing whitespace. LTRIM removes left, RTRIM removes right. Use REPLACE to remove characters from the middle.
  • COALESCE(col, 'default') returns the first non-NULL value. Use for NULL handling in dates and strings.

Definition

Date functions extract parts (YEAR, MONTH), calculate differences (DATEDIFF), and add intervals. String functions join (CONCAT), extract (SUBSTRING), and clean (TRIM, REPLACE) text data.
  • EXTRACT/YEAR/MONTH: Get date parts. Syntax varies by database.
  • DATEDIFF: Date difference. PostgreSQL: date1 - date2. MySQL/SQL Server: DATEDIFF().
  • DATE_ADD/DATEADD: Add interval. PostgreSQL: + INTERVAL. MySQL: DATE_ADD().
  • CONCAT: Join strings. Use CONCAT(), ||, or + depending on database.
  • SUBSTRING: Extract portion. 1-based positions.
  • TRIM/REPLACE: Clean whitespace or substitute characters.
  • COALESCE: First non-NULL value. Essential for NULL handling.

This section is a focused tutorial on Date, Time, and String Functions in SQL. Each question teaches you to think through the problem before writing SQL, build the query step by step, understand how the database executes it, and optimize for performance.

Understanding the Concepts

## Date, Time, and String Functions in SQL

Date functions extract parts (YEAR, MONTH, DAY), calculate differences (DATEDIFF), add intervals (DATE_ADD), and filter by ranges. String functions manipulate text: CONCAT joins strings, SUBSTRING extracts portions, REPLACE substitutes, TRIM removes whitespace, UPPER/LOWER changes case.

Sample Tables

employees table with date and text columns (shared across all queries in this section):

Uses the employees table defined in the Basics section. Date functions work on join_date. String functions work on name and city columns. Syntax varies between PostgreSQL, MySQL, and SQL Server, noted where relevant.

---

Q1. Write a query to find the difference in days between two dates in the same table

Problem: Write a query to find the difference in days between two dates in the same table.

How to think before writing:

  1. Identify which table has the data I need.
  2. What columns do I want in the output?
  3. What condition filters the correct rows?

Pseudo query (plain English first):

TEXT
GET columns FROM table

SQL Solution:

SQL
SELECT id, DATEDIFF(day, start_date, end_date) AS 
days_difference 
FROM projects; 
 
 
Note: DATEDIFF syntax varies — replace accordingly 
(e.g., DATEDIFF('day', start_date, end_date) in some 
systems).

Step-by-step how the database executes this:

  1. FROM: Reads table projects.
  2. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.

---

Q2. Calculate the moving average of salaries over the last 3 employees ordered by hire date

Problem: Calculate the moving average of salaries over the last 3 employees ordered by hire date.

How to think before writing:

  1. Do I need a calculation per row without collapsing groups? That means a window function with OVER().
  2. What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
  3. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
SELECT name, hire_date, salary, 
       AVG(salary) OVER (ORDER BY hire_date 
ROWS BETWEEN 2 PRECEDING AND CURRENT 
ROW) AS moving_avg_salary 
FROM employees;

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. ORDER BY: Sorts the final result set.

Optimization: Window functions scan the partition. Index the PARTITION BY and ORDER BY columns to speed up the sort.

---

Q3. Write a query to generate a calendar table with all dates for the current year

Problem: Write a query to generate a calendar table with all dates for the current year.

How to think before writing:

  1. Identify which table has the data I need.
  2. What columns do I want in the output?
  3. What condition filters the correct rows?

Pseudo query (plain English first):

TEXT
GET columns FROM table

SQL Solution:

SQL
SELECT generate_series( 
  DATE_TRUNC('year', CURRENT_DATE),  
  DATE_TRUNC('year', CURRENT_DATE) + 
INTERVAL '1 year' - INTERVAL '1 day',  
  INTERVAL '1 day' 
) AS calendar_date;

Step-by-step how the database executes this:

1. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.

---

Q4. Write a query to display all employees who have worked on a project longer than 6 months

Problem: Write a query to display all employees who have worked on a project longer than 6 months.

How to think before writing:

1. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT employee_id 
FROM project_assignments 
WHERE end_date - start_date > INTERVAL '6 
months';

Step-by-step how the database executes this:

  1. FROM: Reads table project_assignments.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the WHERE column(s) to avoid a full table scan.

---

Q5. List employees who have been in the company for more than 10 years

Problem: List employees who have been in the company for more than 10 years.

How to think before writing:

1. What condition filters the rows I need? Write the WHERE clause for that condition.

Pseudo query (plain English first):

TEXT
GET columns FROM table
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT * 
FROM employees 
WHERE CURRENT_DATE - hire_date > INTERVAL 
'10 years';

Step-by-step how the database executes this:

  1. FROM: Reads table employees.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the WHERE column(s) to avoid a full table scan.

---

Q6. Find customers with no orders in the last year

Problem: Find customers with no orders in the last year.

How to think before writing:

  1. Can I solve the inner part first as a subquery, then use the result in the outer query?
  2. What condition filters the rows I need? Write the WHERE clause for that condition.
  3. Are there duplicates I need to remove? Use DISTINCT.

Pseudo query (plain English first):

TEXT
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result

SQL Solution:

SQL
SELECT customer_id 
FROM customers 
WHERE customer_id NOT IN ( 
  SELECT DISTINCT customer_id FROM orders 
WHERE order_date >= CURRENT_DATE - 
INTERVAL '1 year' 
);

Step-by-step how the database executes this:

  1. FROM: Reads table customers.
  2. WHERE: Filters individual rows. Rows failing the condition are discarded.
  3. SELECT: Projects the final columns and evaluates expressions/aliases.
  4. DISTINCT: Removes duplicate rows by comparing all selected columns.

Optimization: If the subquery runs once per outer row (correlated), consider rewriting as a JOIN or CTE for better performance.

---

Q7. Find the top 5 longest projects

Problem: Find the top 5 longest projects.

How to think before writing:

1. How should the result be sorted? ASC (ascending) or DESC (descending)?

Pseudo query (plain English first):

TEXT
GET columns FROM table
SORT the result

SQL Solution:

SQL
SELECT project_id, start_date, end_date, 
       end_date - start_date AS duration 
FROM projects 
ORDER BY duration DESC 
LIMIT 5;

Step-by-step how the database executes this:

  1. FROM: Reads table projects.
  2. SELECT: Projects the final columns and evaluates expressions/aliases.
  3. ORDER BY: Sorts the final result set.
  4. LIMIT/TOP: Returns only the first N rows from the sorted result.

Optimization: Run EXPLAIN ANALYZE to check the execution plan and add indexes where needed.

---

Q8. Find products ordered on their launch date

Problem: Find products ordered on their launch date.

How to think before writing:

1. Which tables do I need? How are they related? What column connects them?

Pseudo query (plain English first):

TEXT
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition

SQL Solution:

SQL
SELECT o.product_id 
FROM orders o 
JOIN products p ON o.product_id = p.product_id 
WHERE CAST(o.order_date AS DATE) = 
p.launch_date;

Step-by-step how the database executes this:

  1. FROM: Reads table orders.
  2. INNER JOIN: Combines rows from joined tables using the ON condition.
  3. WHERE: Filters individual rows. Rows failing the condition are discarded.
  4. SELECT: Projects the final columns and evaluates expressions/aliases.

Optimization: Add an index on the JOIN columns. Ensure both sides of ON are indexed.

---

Key Takeaway

This section covered 8 interview questions on Date, Time, and String Functions in SQL. Each question followed the same learning pattern: understand the problem, think about the approach, write a pseudo query in plain English, translate to SQL, understand the execution order, and consider optimization.

Date functions extract parts (YEAR, MONTH, DAY), calculate differences (DATEDIFF), add intervals (DATE_ADD), and filter by ranges. String functions manipulate text: CONCAT joins strings, SUBSTRING extracts portions, REPLACE substitutes, TRIM removes whitespace, UPPER/LOWER changes case.

Practice each query on the shared sample table. If you can solve all 8 without looking at the answer, you have mastered this concept.

---

Share
Use ← → arrow keys to navigate