🎓 Velnov

🗄️ SQL · Topic 10 of 20

Date & Time Functions — SQL Questions with Answers

30 practice questions on Date & Time Functions, each with the correct answer and a clear explanation of why it is right. Read through them as revision notes, or take this topic as an interactive quiz to test yourself.

2 easy · 17 medium · 11 hard · Part of SQL: Databases from basics to window functions - 20 topics.

Key concepts in this topic

NOWCURRENT_TIMESTAMPISO 8601Date formatEXTRACTYEARDATE_ADDINTERVALDATEDIFFDate arithmeticDATETIMERangesGotchasDATE_TRUNCReportingGROUP BYDay of weekDATECASTUTCTimezonesBest practicesAGEDate ranges

Jump to: Easy · Medium · Hard

Easy questions (2)

Q1. easy

Which function returns the current date and time?

SELECT NOW();   -- 2026-07-04 14:30:05
  1. A NOW() (or CURRENT_TIMESTAMP)
  2. B TODAY()
  3. C DATE()
  4. D TIME.NOW

Answer: A — NOW() (or CURRENT_TIMESTAMP)

NOW() and CURRENT_TIMESTAMP return the current moment. CURRENT_DATE gives just the date, and CURRENT_TIME just the clock. (SQL Server uses GETDATE().)

NOWCURRENT_TIMESTAMP

Q2. easy

What is the standard SQL date format?

WHERE order_date = '2026-07-04';
  1. A 'YYYY-MM-DD' - year, month, day
  2. B 'DD-MM-YYYY'
  3. C 'MM/DD/YYYY'
  4. D 'DD/MM/YY'

Answer: A — 'YYYY-MM-DD' - year, month, day

ISO 8601 format 'YYYY-MM-DD' is unambiguous and sorts correctly as text. Always use it in queries to avoid locale confusion between month and day.

ISO 8601Date format

Medium questions (17)

Q3. medium

How do you extract just the YEAR from a date?

SELECT EXTRACT(YEAR FROM order_date), EXTRACT(MONTH FROM order_date);
  1. A EXTRACT(YEAR FROM order_date) or YEAR(order_date)
  2. B GET_YEAR(order_date)
  3. C order_date.year
  4. D DATEYEAR(order_date)

Answer: A — EXTRACT(YEAR FROM order_date) or YEAR(order_date)

EXTRACT is standard SQL; MySQL and SQL Server also have YEAR(), MONTH(), DAY() shortcuts. EXTRACT(DOW FROM d) even gives the day of week.

EXTRACTYEAR

Q4. medium

How do you add 7 days to a date?

SELECT order_date + INTERVAL '7 days' AS due_date FROM orders;
  1. A order_date + INTERVAL '7 days' or DATE_ADD(order_date, INTERVAL 7 DAY)
  2. B order_date + 7 (always works)
  3. C ADD_DAYS(order_date, 7)
  4. D order_date.plus(7)

Answer: A — order_date + INTERVAL '7 days' or DATE_ADD(order_date, INTERVAL 7 DAY)

PostgreSQL/Oracle use INTERVAL; MySQL uses DATE_ADD; SQL Server uses DATEADD(DAY, 7, order_date). Plain + 7 works on DATE in some databases but is not portable.

DATE_ADDINTERVAL

Q5. medium

How do you find the number of days between two dates?

SELECT DATEDIFF(delivered_at, ordered_at) AS days_to_deliver FROM orders;
  1. A DATEDIFF(end_date, start_date) or end_date - start_date
  2. B DAYS_BETWEEN only
  3. C SUBTRACT(end, start)
  4. D end.minus(start)

Answer: A — DATEDIFF(end_date, start_date) or end_date - start_date

In PostgreSQL, subtracting two DATEs gives an integer number of days. MySQL uses DATEDIFF(end, start); SQL Server uses DATEDIFF(DAY, start, end) - note the argument order differs.

DATEDIFFDate arithmetic

Q7. medium

How do you get the first day of the current month?

SELECT DATE_TRUNC('month', order_date) AS month, COUNT(*)
FROM orders GROUP BY 1;
  1. A DATE_TRUNC('month', CURRENT_DATE) in PostgreSQL
  2. B MONTH_START()
  3. C FIRST_DAY()
  4. D CURRENT_DATE - 30

Answer: A — DATE_TRUNC('month', CURRENT_DATE) in PostgreSQL

DATE_TRUNC rounds a date down to a unit (month, year, hour). It is the cleanest way to bucket timestamps for reporting. MySQL uses DATE_FORMAT or arithmetic.

DATE_TRUNCReporting

Q9. medium

How do you extract the day of the week?

  1. A EXTRACT(DOW FROM date) or DAYOFWEEK(date) - returns a number for the weekday
  2. B WEEKDAY_NAME(date)
  3. C DAY(date)
  4. D There is no way

Answer: A — EXTRACT(DOW FROM date) or DAYOFWEEK(date) - returns a number for the weekday

Beware the numbering differs: PostgreSQL DOW is 0=Sunday, MySQL DAYOFWEEK is 1=Sunday, and ISODOW is 1=Monday. Check your database before filtering on weekdays.

Day of weekEXTRACT

Q10. medium

What does DATE(order_datetime) return?

SELECT DATE(created_at) AS day, COUNT(*) FROM events GROUP BY 1;
  1. A The date portion only, dropping the time
  2. B The full timestamp
  3. C Just the year
  4. D An error

Answer: A — The date portion only, dropping the time

Casting a DATETIME to DATE (via DATE() or CAST(x AS DATE)) strips the time - handy for grouping all of a day's events together.

DATECAST

Q12. medium

How do you calculate age in years from a birthdate?

  1. A AGE(birthdate) in PostgreSQL, or DATEDIFF-based arithmetic elsewhere
  2. B birthdate - NOW()
  3. C YEARS(birthdate)
  4. D birthdate.age()

Answer: A — AGE(birthdate) in PostgreSQL, or DATEDIFF-based arithmetic elsewhere

PostgreSQL's AGE() returns an interval; extract the years. Elsewhere: FLOOR(DATEDIFF(NOW(), birthdate) / 365.25), being careful about leap years and exact birthdays.

AGEDATEDIFF

Q13. medium

How do you filter for records from the last 30 days?

WHERE created_at >= NOW() - INTERVAL '30 days';
  1. A WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  2. B WHERE created_at = LAST_30_DAYS
  3. C WHERE created_at IN (30 days)
  4. D WHERE RECENT(created_at, 30)

Answer: A — WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'

Compute the cutoff by subtracting an interval from today, then compare. Keeping the function on the constant side (not the column) lets the index on created_at work.

INTERVALDate ranges

Q15. medium

How do you format a date as 'July 04, 2026'?

  1. A TO_CHAR(order_date, 'Month DD, YYYY') in PostgreSQL/Oracle
  2. B FORMAT(order_date)
  3. C STRING(order_date)
  4. D Dates cannot be formatted

Answer: A — TO_CHAR(order_date, 'Month DD, YYYY') in PostgreSQL/Oracle

TO_CHAR with a format model handles custom display. MySQL uses DATE_FORMAT(date, '%M %d, %Y'); SQL Server uses FORMAT(). Prefer formatting in the app layer where possible.

TO_CHARDATE_FORMAT

Q17. medium

How do you get yesterday's date?

  1. A CURRENT_DATE - INTERVAL '1 day' (or CURRENT_DATE - 1 in some databases)
  2. B YESTERDAY()
  3. C NOW() - 1
  4. D PREVIOUS(CURRENT_DATE)

Answer: A — CURRENT_DATE - INTERVAL '1 day' (or CURRENT_DATE - 1 in some databases)

Subtracting an interval is the portable form. In PostgreSQL and MySQL, CURRENT_DATE - 1 also works because a DATE minus an integer subtracts days.

INTERVALDate arithmetic

Q19. medium

What does EXTRACT(EPOCH FROM interval) give in PostgreSQL?

  1. A The interval expressed in total SECONDS
  2. B The year
  3. C The month name
  4. D An error

Answer: A — The interval expressed in total SECONDS

EPOCH converts an interval or timestamp to seconds - useful for computing precise durations like average response time: EXTRACT(EPOCH FROM (ended - started)).

EPOCHDuration

Q20. medium

How do you get the last day of a month?

  1. A LAST_DAY(date) in MySQL/Oracle, or date-arithmetic with DATE_TRUNC in PostgreSQL
  2. B MONTH_END(date)
  3. C date + 30
  4. D FINAL_DAY(date)

Answer: A — LAST_DAY(date) in MySQL/Oracle, or date-arithmetic with DATE_TRUNC in PostgreSQL

LAST_DAY handles varying month lengths and leap years automatically. In PostgreSQL: (DATE_TRUNC('month', d) + INTERVAL '1 month - 1 day').

LAST_DAYMonth boundaries

Q22. medium

How would you count orders per weekday name?

SELECT TO_CHAR(order_date, 'Day') AS weekday, COUNT(*)
FROM orders GROUP BY 1;
  1. A GROUP BY the weekday using TO_CHAR(date, 'Day') or DAYNAME(date)
  2. B GROUP BY date
  3. C GROUP BY hour
  4. D It cannot be done

Answer: A — GROUP BY the weekday using TO_CHAR(date, 'Day') or DAYNAME(date)

Extract the weekday name (or number) and group by it. To order Monday-Sunday correctly, group by the numeric day of week and format the label separately.

WeekdayGROUP BY

Q24. medium

What does DATE_TRUNC('hour', ts) do?

  1. A Rounds the timestamp DOWN to the start of the hour (minutes and seconds become 0)
  2. B Rounds to the nearest hour
  3. C Returns the hour number only
  4. D Adds an hour

Answer: A — Rounds the timestamp DOWN to the start of the hour (minutes and seconds become 0)

Truncation always rounds down, making it perfect for bucketing events into hourly (or minutely) intervals for time-series charts.

DATE_TRUNCTime series

Q26. medium

How do you extract the hour from a timestamp?

  1. A EXTRACT(HOUR FROM ts) or HOUR(ts)
  2. B ts.hour
  3. C GET_HOUR(ts)
  4. D TIMEPART(ts)

Answer: A — EXTRACT(HOUR FROM ts) or HOUR(ts)

Extracting the hour is common for finding peak-traffic times: GROUP BY EXTRACT(HOUR FROM created_at) to see activity by hour of day.

EXTRACTHOUR

Q28. medium

How do you find records from the current year?

  1. A WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE)
  2. B WHERE YEAR(order_date) = THIS_YEAR
  3. C WHERE order_date = 2026
  4. D WHERE order_date CONTAINS 2026

Answer: A — WHERE order_date >= DATE_TRUNC('year', CURRENT_DATE)

Truncating today's date to the year gives January 1st, and comparing with >= keeps the index usable - better than wrapping the column in YEAR().

DATE_TRUNCSargability

Q30. medium

Which is the safest, most portable literal for a specific date-time?

  1. A '2026-07-04 14:30:00' - ISO format, understood everywhere
  2. B 'July 4 2026 2:30pm'
  3. C '04-07-26'
  4. D '7/4/26 14:30'

Answer: A — '2026-07-04 14:30:00' - ISO format, understood everywhere

ISO 8601 (YYYY-MM-DD HH:MM:SS) is unambiguous and sorts correctly. The other formats depend on locale settings and are a frequent source of silent misreads.

ISO 8601Literals

Hard questions (11)

Q6. hard

Why does 'WHERE order_date = '2026-07-04'' sometimes miss rows?

  1. A If the column stores a time too, only rows at exactly midnight match; use a date range instead
  2. B The date format is wrong
  3. C Dates cannot be compared with =
  4. D It never misses rows

Answer: A — If the column stores a time too, only rows at exactly midnight match; use a date range instead

A DATETIME of 2026-07-04 14:30 is not equal to 2026-07-04 00:00. Use a half-open range: order_date >= '2026-07-04' AND order_date < '2026-07-05'.

  • One of the most common real-world SQL bugs.

DATETIMERangesGotchas

Q8. hard

How do you group sales by month correctly across multiple years?

  1. A GROUP BY DATE_TRUNC('month', sale_date) - keeps year and month together
  2. B GROUP BY MONTH(sale_date) - merges same months across years
  3. C GROUP BY sale_date
  4. D GROUP BY YEAR(sale_date) only

Answer: A — GROUP BY DATE_TRUNC('month', sale_date) - keeps year and month together

GROUP BY MONTH() alone would merge January 2025 and January 2026 into one bucket. DATE_TRUNC (or grouping by both YEAR and MONTH) keeps them separate.

DATE_TRUNCGROUP BY

Q11. hard

Why is storing timestamps in UTC recommended?

  1. A It avoids ambiguity across timezones and daylight-saving changes; convert to local time only for display
  2. B UTC is faster to store
  3. C Local time never changes
  4. D It uses less space

Answer: A — It avoids ambiguity across timezones and daylight-saving changes; convert to local time only for display

Storing local times means a value can be ambiguous (a clock hour can occur twice) or missing (during a spring-forward). UTC is a single monotonic reference; format it per-user at display time.

UTCTimezonesBest practices

Q14. hard

Why avoid WHERE YEAR(order_date) = 2026 on a large table?

  1. A The function on the column blocks index usage; use a date range instead
  2. B YEAR() is inaccurate
  3. C It returns duplicates
  4. D There is no downside

Answer: A — The function on the column blocks index usage; use a date range instead

Rewrite as order_date >= '2026-01-01' AND order_date < '2027-01-01'. The range compares the raw column, so an index can seek directly to the matching rows.

SargabilityIndexes

Q16. hard

What is the difference between TIMESTAMP and TIMESTAMPTZ (PostgreSQL)?

  1. A TIMESTAMPTZ is timezone-aware and stores an absolute instant; TIMESTAMP stores a 'wall clock' with no zone
  2. B They are identical
  3. C TIMESTAMPTZ cannot store time
  4. D TIMESTAMP is newer

Answer: A — TIMESTAMPTZ is timezone-aware and stores an absolute instant; TIMESTAMP stores a 'wall clock' with no zone

TIMESTAMPTZ converts input to UTC on storage and back to the session zone on display. For anything user-facing across regions, TIMESTAMPTZ is usually the right choice.

TIMESTAMPTZTimezones

Q18. hard

How do you find records created 'today' regardless of time?

  1. A WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + INTERVAL '1 day'
  2. B WHERE created_at = CURRENT_DATE
  3. C WHERE DATE(created_at) = TODAY
  4. D WHERE created_at LIKE '%today%'

Answer: A — WHERE created_at >= CURRENT_DATE AND created_at < CURRENT_DATE + INTERVAL '1 day'

The half-open range captures every moment of today while keeping the index usable. Option B fails because a timestamped value rarely equals midnight exactly.

Date rangesCURRENT_DATE

Q21. hard

What can go wrong comparing a DATE column to a string literal?

  1. A Implicit conversion depends on locale/format settings and can misread the date or block the index
  2. B Nothing ever goes wrong
  3. C It always errors
  4. D Strings are always faster

Answer: A — Implicit conversion depends on locale/format settings and can misread the date or block the index

Always use ISO format 'YYYY-MM-DD', which every database interprets unambiguously. Ambiguous forms like '04/07/2026' may be read as April 7 or July 4.

Implicit conversionISO 8601

Q23. hard

How do you compute a running date range for 'this quarter'?

  1. A DATE_TRUNC('quarter', CURRENT_DATE) for the start, plus INTERVAL '3 months' for the end
  2. B QUARTER(CURRENT_DATE)
  3. C CURRENT_DATE / 4
  4. D It is impossible

Answer: A — DATE_TRUNC('quarter', CURRENT_DATE) for the start, plus INTERVAL '3 months' for the end

DATE_TRUNC supports 'quarter', giving the first day of the current quarter. Add three months for a clean half-open upper bound.

DATE_TRUNCQuarters

Q25. hard

Why can date arithmetic across daylight-saving boundaries surprise you?

  1. A Adding '1 day' vs '24 hours' can differ, because a DST day has 23 or 25 hours
  2. B Dates never change length
  3. C SQL ignores DST always
  4. D It only affects leap years

Answer: A — Adding '1 day' vs '24 hours' can differ, because a DST day has 23 or 25 hours

With timezone-aware types, '+ INTERVAL 1 day' keeps the same wall-clock time, while '+ INTERVAL 24 hours' adds exactly 24 hours. Know which you need for scheduling.

DSTTimezones

Q27. hard

What is a common way to store dates that avoids timezone issues entirely?

  1. A Use a plain DATE type when time-of-day is irrelevant (e.g. a birthday or invoice date)
  2. B Always store as text
  3. C Store as an integer offset
  4. D Never store dates

Answer: A — Use a plain DATE type when time-of-day is irrelevant (e.g. a birthday or invoice date)

A birthday has no meaningful time or timezone, so a DATE is correct and simplest. Reserve timezone-aware timestamps for genuine instants like 'order placed at'.

DATEModelling

Q29. hard

How do you calculate business days between two dates?

  1. A There is no built-in function - you count days and subtract weekends (and holidays) with a calendar table or a generated series
  2. B BUSINESS_DAYS(a, b)
  3. C DATEDIFF handles it automatically
  4. D It is impossible in SQL

Answer: A — There is no built-in function - you count days and subtract weekends (and holidays) with a calendar table or a generated series

Most databases lack a business-day function. The robust approach is a 'calendar' or 'dim_date' table flagging weekends and holidays, then joining and counting working days.

Calendar tableBusiness days

Practice “Date & Time Functions” as a quiz