Days from hire date to a fixed date
Return each employee's name and the number of days from their hire_date to 2026-01-01. Return two columns: name, days.
Date functions differ between databases. This page runs SQLite. There, julianday('2026-01-01') turns a YYYY-MM-DD date into a day number, and subtracting two day numbers gives the days between them. In MySQL you would write DATEDIFF('2026-01-01', hire_date). In PostgreSQL you subtract two dates, after turning the text column into a date: DATE '2026-01-01' - hire_date::date.
Rows can come back in any order. Column names do not matter; the values do.
Sample database: Company: departments, employees and orders
Three tables. departments (id, name, city). employees (id, name, email, dept_id, manager_id, salary, bonus, hire_date): dept_id and manager_id may be NULL, bonus may be NULL, hire_date is text in the form YYYY-MM-DD. orders (id, employee_id, customer, amount, status, order_date): employee_id is the sales rep (an employee id), amount may be NULL, status is DELIVERED, SHIPPED, PENDING or CANCELLED in capitals, and order_date is text in the form YYYY-MM-DD. Foreign keys are not enforced, so an order may point to an employee who does not exist.
| id | name | city |
|---|---|---|
| 1 | Engineering | Bengaluru |
| 2 | QA | Pune |
| 3 | Sales | Mumbai |
| 4 | HR | Pune |
| 5 | Support | Chennai |
| id | name | dept_id | manager_id | salary | bonus | hire_date | |
|---|---|---|---|---|---|---|---|
| 1 | Asha Rao | asha@shop.in | 1 | NULL | 150000 | 20000 | 2018-03-01 |
| 2 | Vikram Shah | vikram@shop.in | 1 | 1 | 95000 | NULL | 2020-07-15 |
| 3 | Neha Gupta | neha@shop.in | 2 | 1 | 82000 | 5000 | 2021-01-10 |
| 4 | Rohan Mehta | rohan@shop.in | 2 | 3 | 64000 | NULL | 2022-06-01 |
| 5 | Priya Nair | priya@shop.in | 2 | 3 | 64000 | 3000 | 2023-02-20 |
| 6 | Arjun Das | arjun@shop.in | 3 | 1 | 70000 | 15000 | 2019-11-05 |
| 7 | Kavya Iyer | kavya@shop.in | 3 | 6 | 52000 | NULL | 2024-01-08 |
| 8 | Imran Khan | imran@shop.in | 4 | 1 | 58000 | 2000 | 2020-09-30 |
| 9 | Sneha Patil | neha@shop.in | NULL | 1 | 47000 | NULL | 2025-04-14 |
| 10 | Dev Malhotra | dev@shop.in | 1 | 2 | 95000 | 8000 | 2022-12-01 |
| id | employee_id | customer | amount | status | order_date |
|---|---|---|---|---|---|
| 101 | 6 | Acme Ltd | 12000 | DELIVERED | 2026-01-05 |
| 102 | 6 | Bharat Traders | 4500 | CANCELLED | 2026-01-09 |
| 103 | 7 | Acme Ltd | 8000 | DELIVERED | 2026-02-11 |
| 104 | 7 | Cosmo Retail | 15000 | SHIPPED | 2026-02-20 |
| 105 | 6 | Delta Foods | 3000 | PENDING | 2026-03-02 |
| 106 | 7 | Bharat Traders | 9500 | DELIVERED | 2026-03-15 |
| 107 | 12 | Echo Stores | 7000 | DELIVERED | 2026-03-18 |
| 108 | 6 | Acme Ltd | NULL | PENDING | 2026-04-01 |
| 109 | 7 | Cosmo Retail | 15000 | SHIPPED | 2026-02-20 |
| 110 | 8 | Fresh Mart | 2500 | DELIVERED | 2026-04-10 |
Run shows your rows. Submit compares them with the expected rows.