SQL Date Functions and Date Edge Cases: Working Days, Month-End, Fiscal Year, Time Zones

Dates cause more subtle SQL bugs than any other data type. This guide covers the everyday SQL date functions, then the edge cases interviewers and real reports care about: counting working days, month-end totals, fiscal years that don't start in January, and storing times correctly across time zones.

Date Functions

-- Current date and time
SELECT NOW();              -- 2025-05-09 14:32:10
SELECT CURDATE();          -- 2025-05-09  (MySQL)
SELECT CURRENT_DATE;       -- Standard SQL
SELECT SYSDATE FROM dual;  -- Oracle

-- Extract parts of a date
SELECT YEAR(hire_date) AS year, MONTH(hire_date) AS month FROM employees;
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;  -- Standard

-- Date arithmetic
SELECT name, DATEDIFF(NOW(), hire_date) AS days_employed FROM employees;
SELECT name, TIMESTAMPDIFF(YEAR, dob, NOW()) AS age FROM employees;

-- Add/subtract dates
SELECT DATE_ADD(NOW(), INTERVAL 30 DAY) AS due_date;
SELECT DATE_SUB(NOW(), INTERVAL 1 YEAR) AS one_year_ago;

-- Format dates
SELECT DATE_FORMAT(hire_date, '%d-%m-%Y') AS formatted_date FROM employees;
-- Output: 09-05-2022

-- Day of week, month name
SELECT DAYNAME(order_date) AS day_name, MONTHNAME(order_date) AS month_name
FROM orders;

-- Employees hired in the last 30 days
SELECT * FROM employees WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Date and Time Edge Cases

Working Days Calculation (Exclude Weekends)

-- Count working days between two dates (exclude Sat & Sun)
-- Method: generate all dates, filter to weekdays, count them
WITH RECURSIVE date_range AS (
    SELECT CAST('2025-01-01' AS DATE) AS dt
    UNION ALL
    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM date_range
    WHERE dt < '2025-01-31'
)
SELECT COUNT(*) AS working_days
FROM date_range
WHERE DAYOFWEEK(dt) NOT IN (1, 7);  -- 1=Sunday, 7=Saturday

-- DAYOFWEEK: 1=Sun, 2=Mon, 3=Tue, 4=Wed, 5=Thu, 6=Fri, 7=Sat
-- WEEKDAY:   0=Mon, 1=Tue, 2=Wed, 3=Thu, 4=Fri, 5=Sat, 6=Sun

-- Quick formula (approximate, without recursive CTE)
-- 5/7 of total days ≈ working days (rough estimate only)
SELECT FLOOR(DATEDIFF('2025-01-31', '2025-01-01') * 5 / 7) AS approx_working_days;

-- Orders that are overdue by more than 3 WORKING days
WITH RECURSIVE d AS (
    SELECT o.order_id, o.due_date, o.due_date AS check_date, 0 AS work_days
    FROM orders o WHERE o.status = 'pending'
    UNION ALL
    SELECT order_id, due_date,
           DATE_ADD(check_date, INTERVAL 1 DAY),
           work_days + IF(DAYOFWEEK(DATE_ADD(check_date,INTERVAL 1 DAY)) NOT IN (1,7),1,0)
    FROM d WHERE check_date < CURDATE() AND work_days < 10
)
SELECT order_id, due_date, MAX(work_days) AS overdue_working_days
FROM d GROUP BY order_id, due_date HAVING MAX(work_days) > 3;

Last Day of Month & Month-End Reporting

-- Last day of the current month
SELECT LAST_DAY(NOW()) AS month_end;

-- First day of current month
SELECT DATE_FORMAT(NOW(), '%Y-%m-01') AS month_start;

-- First day of next month
SELECT DATE_ADD(LAST_DAY(NOW()), INTERVAL 1 DAY) AS next_month_start;

-- All orders from the last COMPLETE month
SELECT * FROM orders
WHERE order_date >= DATE_FORMAT(DATE_SUB(NOW(), INTERVAL 1 MONTH), '%Y-%m-01')
  AND order_date < DATE_FORMAT(NOW(), '%Y-%m-01');

-- Monthly summary: always complete months only
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
       COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders
WHERE order_date < DATE_FORMAT(NOW(), '%Y-%m-01')  -- exclude current incomplete month
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

Fiscal Year Queries

-- Fiscal Year April 1 to March 31 (Indian financial year)
-- Determine fiscal year for each transaction
SELECT transaction_date, amount,
    CASE
        WHEN MONTH(transaction_date) >= 4
        THEN YEAR(transaction_date)
        ELSE YEAR(transaction_date) - 1
    END AS fiscal_year,
    CASE
        WHEN MONTH(transaction_date) >= 4
        THEN CONCAT('Q', FLOOR((MONTH(transaction_date) - 4) / 3) + 1)
        ELSE CONCAT('Q', FLOOR((MONTH(transaction_date) + 8) / 3) + 1)
    END AS fiscal_quarter
FROM transactions;

-- Revenue for fiscal year 2024-25 (Apr 2024 - Mar 2025)
SELECT SUM(amount) AS fy2425_revenue
FROM transactions
WHERE transaction_date BETWEEN '2024-04-01' AND '2025-03-31';

-- US fiscal year Oct 1 - Sep 30
SELECT order_date,
       CASE WHEN MONTH(order_date) >= 10 THEN YEAR(order_date) + 1
            ELSE YEAR(order_date)
       END AS us_fiscal_year
FROM orders;

Timezone Handling

-- Store all timestamps in UTC, convert on display

-- MySQL: Convert UTC to IST (UTC+5:30)
SELECT order_id,
       order_date AS utc_time,
       CONVERT_TZ(order_date, '+00:00', '+05:30') AS ist_time
FROM orders;

-- Current time in different timezones
SELECT NOW()                                        AS server_time,
       CONVERT_TZ(NOW(), @@session.time_zone, '+00:00') AS utc,
       CONVERT_TZ(NOW(), @@session.time_zone, '+05:30') AS ist,
       CONVERT_TZ(NOW(), @@session.time_zone, '-05:00') AS est;

-- PostgreSQL: timestamp with time zone
SELECT NOW() AT TIME ZONE 'UTC' AS utc_time,
       NOW() AT TIME ZONE 'Asia/Kolkata' AS ist_time;

-- Best practice: always store TIMESTAMP (UTC) not DATETIME (no timezone)
-- TIMESTAMP: auto-converts to UTC on store, back to session tz on read
-- DATETIME:  stores exactly what you insert — no timezone awareness

FAQs

How do you get the last day of the month in SQL?

MySQL has LAST_DAY(date); in PostgreSQL use (DATE_TRUNC('month', d) + INTERVAL '1 month - 1 day')::date; SQL Server has EOMONTH(date).

Should I use TIMESTAMP or DATETIME in MySQL?

TIMESTAMP stores UTC and converts to the session time zone, but its range ends in 2038; DATETIME stores the literal value with a much wider range. Many teams store UTC in DATETIME and convert in the application.

How do you count working days between two dates?

Generate the dates with a recursive CTE or a calendar table, exclude weekends with DAYOFWEEK (and holidays from a holiday table), and count the rest.