Definition
- 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:
- Identify which table has the data I need.
- What columns do I want in the output?
- What condition filters the correct rows?
Pseudo query (plain English first):
GET columns FROM table
SQL Solution:
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:
- FROM: Reads table
projects. - 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:
- Do I need a calculation per row without collapsing groups? That means a window function with OVER().
- What defines the group? That goes in PARTITION BY. What defines the order? That goes in ORDER BY inside OVER().
- How should the result be sorted? ASC (ascending) or DESC (descending)?
Pseudo query (plain English first):
GET columns FROM table
SORT the result
SQL Solution:
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:
- FROM: Reads table
employees. - Window Function + OVER(): Computes value per row across its partition. Rows are NOT collapsed.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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:
- Identify which table has the data I need.
- What columns do I want in the output?
- What condition filters the correct rows?
Pseudo query (plain English first):
GET columns FROM table
SQL Solution:
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):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT employee_id
FROM project_assignments
WHERE end_date - start_date > INTERVAL '6
months';
Step-by-step how the database executes this:
- FROM: Reads table
project_assignments. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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):
GET columns FROM table
KEEP ONLY rows that satisfy the condition
SQL Solution:
SELECT *
FROM employees
WHERE CURRENT_DATE - hire_date > INTERVAL
'10 years';
Step-by-step how the database executes this:
- FROM: Reads table
employees. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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:
- Can I solve the inner part first as a subquery, then use the result in the outer query?
- What condition filters the rows I need? Write the WHERE clause for that condition.
- Are there duplicates I need to remove? Use DISTINCT.
Pseudo query (plain English first):
INNER QUERY: Find the value I need to compare against
OUTER QUERY: Use that value to filter the main result
SQL Solution:
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:
- FROM: Reads table
customers. - WHERE: Filters individual rows. Rows failing the condition are discarded.
- SELECT: Projects the final columns and evaluates expressions/aliases.
- 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):
GET columns FROM table
SORT the result
SQL Solution:
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:
- FROM: Reads table
projects. - SELECT: Projects the final columns and evaluates expressions/aliases.
- ORDER BY: Sorts the final result set.
- 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):
GET columns FROM table
COMBINE WITH another table WHERE they match
KEEP ONLY rows that satisfy the condition
SQL Solution:
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:
- FROM: Reads table
orders. - INNER JOIN: Combines rows from joined tables using the ON condition.
- WHERE: Filters individual rows. Rows failing the condition are discarded.
- 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.
---