SQL Date and Time Functions (GETDATE, DATEADD, DATEDIFF, FORMAT, ...)

🔹 Why Learn Date & Time Functions?

Reporting, scheduling, and analytics depend on accurate timestamps. SQL offers portable functions to get the current time, add or subtract intervals, compute differences, format output, and extract components.

⚙️ Common Date & Time Functions

🧠 Example 1 – Current Date and Time

SELECT
  GETDATE()          AS current_datetime,
  CURRENT_TIMESTAMP  AS current_timestamp,
  NOW()              AS mysql_now;

🧠 Example 2 – Add or Subtract Dates

SELECT
  GETDATE() AS today,
  DATEADD(DAY, 7, GETDATE())   AS next_week,
  DATEADD(MONTH, -1, GETDATE()) AS last_month;

🧠 Example 3 – Differences Between Dates

SELECT
  DATEDIFF(DAY,   '2025-10-01', '2025-10-24') AS days_diff,
  DATEDIFF(MONTH, '2025-01-01', '2025-10-24') AS months_diff,
  DATEDIFF(YEAR,  '2020-10-24', '2025-10-24') AS years_diff;

🧠 Example 4 – Extract Components

SELECT
  DATEPART(YEAR,  GETDATE()) AS year_part,
  DATEPART(MONTH, GETDATE()) AS month_part,
  DATEPART(DAY,   GETDATE()) AS day_part;

🧠 Example 5 – Format Dates

SELECT
  FORMAT(GETDATE(), 'yyyy-MM-dd') AS iso_format,
  FORMAT(GETDATE(), 'dd-MMM-yyyy') AS readable_format,
  FORMAT(GETDATE(), 'hh:mm tt')    AS time_format;

🧠 Example 6 – Filter by Date Range

SELECT *
FROM orders
WHERE order_date BETWEEN '2025-10-01' AND '2025-10-24';

🧠 Example 7 – Start and End of Month

SELECT
  DATEADD(DAY, -DAY(GETDATE()) + 1, GETDATE()) AS start_of_month,
  EOMONTH(GETDATE()) AS end_of_month;

⚙️ Bonus – Time Differences in Smaller Units

SELECT
  DATEDIFF(HOUR,   '2025-10-24 08:00:00', '2025-10-24 14:30:00') AS hours_diff,
  DATEDIFF(MINUTE, '2025-10-24 08:00:00', '2025-10-24 14:30:00') AS minutes_diff;

⚖️ Summary Cheat Sheet

💡 Interview Soundbite