Working with Dates

Date Functions — Essentials

SQL — Date Functions
— Current date and time
SELECT CURRENT_DATE; — 2026-04-24
SELECT CURRENT_TIMESTAMP; — 2026-04-24 10:30:00
SELECT NOW(); — same as CURRENT_TIMESTAMP
 
— Last 30 days of orders
SELECT * FROM orders
WHERE order_date >= CURRENT_DATE – INTERVAL ’30 days’;
 
— MySQL syntax
WHERE order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);
Date Parts

EXTRACT — Get Parts of a Date

SQL — EXTRACT
SELECT
  order_date,
  EXTRACT(YEAR FROM order_date) AS year_,
  EXTRACT(MONTH FROM order_date) AS month_,
  EXTRACT(DAY FROM order_date) AS day_,
  EXTRACT(DOW FROM order_date) AS day_of_week
FROM orders;
 
— Group orders by month
SELECT EXTRACT(MONTH FROM order_date) AS month_,
  SUM(order_amount) AS monthly_revenue
FROM orders
GROUP BY EXTRACT(MONTH FROM order_date)
ORDER BY month_;
 
— MySQL equivalent: YEAR(), MONTH(), DAY()
SELECT YEAR(order_date), MONTH(order_date) FROM orders;
Date Arithmetic

Date Differences & DATEDIFF

SQL — Date Differences
— Days between order and delivery (PostgreSQL)
SELECT
  order_id,
  delivery_date – order_date AS delivery_days
FROM orders;
 
— MySQL DATEDIFF
SELECT DATEDIFF(delivery_date, order_date) AS delivery_days
FROM orders;
 
— Customer tenure in days
SELECT customer_name,
  CURRENT_DATE – join_date AS days_as_customer
FROM customers;
Difference by database: PostgreSQL: date1 – date2 gives days. MySQL: DATEDIFF(end, start). BigQuery: DATE_DIFF(end, start, DAY). Always clarify which SQL dialect you’re using in the interview.
String Functions

String Functions for Data Cleaning

FunctionPurposeExample
UPPER(col)Convert to uppercaseUPPER('mumbai') → 'MUMBAI'
LOWER(col)Convert to lowercaseLOWER('DELHI') → 'delhi'
TRIM(col)Remove leading/trailing spacesTRIM(' hello ') → 'hello'
LENGTH(col)Count charactersLENGTH('SQL') → 3
SUBSTR(col,s,n)Extract n chars from position sSUBSTR('INDIA',1,3) → 'IND'
CONCAT(a,b)Join two stringsCONCAT(first,' ',last)
REPLACE(c,old,new)Replace a string partREPLACE(phone,'+91','')
SPLIT_PART(s,d,n)Split by delimiterSPLIT_PART(email,'@',1)
SQL — String Cleaning
— Clean customer names: trim + proper case
SELECT
  TRIM(customer_name) AS clean_name,
  LOWER(TRIM(email)) AS clean_email,
  REPLACE(phone, ‘+91’, ”) AS local_phone
FROM customers;
 
— Extract domain from email
SELECT email,
  SPLIT_PART(email, ‘@’, 2) AS email_domain
FROM customers;
Interview Questions

Practice Questions — Date & String

Easy
Find all orders placed in January 2024.
InfosysWipro
+
Use EXTRACT for the month and year, or BETWEEN with date strings.
SELECT * FROM orders
WHERE EXTRACT(YEAR FROM order_date) = 2024
  AND EXTRACT(MONTH FROM order_date) = 1;
Medium
Find the average number of days between order placement and delivery for each category.
AmazonFlipkart
+
Use AVG with date subtraction. Group by category for per-category averages.
SELECT category,
  ROUND(AVG(delivery_date – order_date), 1) AS avg_delivery_days
FROM orders
WHERE delivery_date IS NOT NULL
GROUP BY category
ORDER BY avg_delivery_days;
Hard
Find monthly revenue for 2024 and compare to same month in 2023.
GooglePaytm
+
Use two CTEs — one per year — then JOIN on month number.
WITH y24 AS (
  SELECT EXTRACT(MONTH FROM order_date) m, SUM(order_amount) rev
  FROM orders WHERE EXTRACT(YEAR FROM order_date)=2024 GROUP BY m
), y23 AS (
  SELECT EXTRACT(MONTH FROM order_date) m, SUM(order_amount) rev
  FROM orders WHERE EXTRACT(YEAR FROM order_date)=2023 GROUP BY m
)
SELECT a.m, a.rev rev_2024, b.rev rev_2023,
  ROUND((a.rev-b.rev)*100.0/b.rev,2) yoy_pct
FROM y24 a JOIN y23 b ON a.m=b.m;

Ready to ace date & string questions?

Book a free SQL mock session with real date function questions from top companies.

Book Free Session
← PreviousWindow FunctionsNext →50+ Interview Questions