SQL Date and Time Functions: Ranges, Parts and Time Zones

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

Separate a date, a time of day and a timestamp. The sample created_on values are dates, so they have no saved clock time or time-zone offset. An application event timestamp may require an instant in time, a local wall-clock representation, or both. A calendar month in the user’s time zone can start at a different instant from the same month in UTC. Define that reporting zone before grouping events into days or months.

Use half-open ranges for periods: created_on >= '2026-01-01' AND created_on < '2026-02-01' includes all January dates. The same pattern is safer for timestamps than BETWEEN with a final midnight or an invented last fractional second. It also leaves the column unwrapped by a date-extraction function, which can help a normal index satisfy a range filter. Type and parameter handling should be appropriate for the engine rather than relying on ambiguous regional date strings.

Extracting a year or month can be useful for labels and grouping. PostgreSQL uses EXTRACT, MySQL offers YEAR and MONTH as well as EXTRACT, SQL Server uses DATEPART and related functions, and SQLite commonly uses strftime. These are dialect choices, not one universal function spelling. Formatting a date as a month name produces text and may have locale-dependent behaviour; retain a chronological key when those labels need sorting.

Adding a month is not always the same as adding thirty days. End-of-month dates and leap years require the engine’s documented calendar rules. Timestamp differences can count elapsed duration or crossed calendar boundaries, depending on the function. SQL Server DATEDIFF counts boundaries rather than simply returning fractional elapsed units. PostgreSQL intervals, MySQL DATE_ADD, SQL Server DATEADD and SQLite modifiers have distinct syntax. Test the boundary dates relevant to the actual application, including daylight-saving changes when working with local times.

The shown date range is suitable for these sample date values in all four systems. SQLite has no dedicated native date storage type; its date functions work with supported text, Julian-day or Unix-time representations as specified. Use strftime in SQLite, EXTRACT in PostgreSQL, and documented dialect functions in MySQL and SQL Server for parts and arithmetic.

Check the engine-specific syntax and version requirements in SQLite date and time functions, PostgreSQL temporal functions.

Return the February sales using a half-open range ending at March 1. Keep the saved date values in the output.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT id,created_on FROM sales
WHERE created_on >= '2026-02-01'
 AND created_on < '2026-03-01' ORDER BY id;

id | created_on
104 | 2026-02-01
105 | 2026-02-02
106 | 2026-02-03

The three February rows lie on or after the included start and before the excluded next-month start. This includes the first day correctly and would exclude a March 1 row. Because the sample field is date-only, no timezone conversion is implied by this query.

Change the January filter’s upper bound from exclusive February 1 to inclusive February 1 and predict that sale 104 now enters. Restore the half-open form and compare January and February as adjacent nonoverlapping ranges. In a separate timestamp example, place a value at noon on the final day to show why an upper midnight cutoff is not a full-day boundary.