In the world of databases, dates hold secrets waiting to be unlocked. Welcome to an exploration of time-based SQL queries in Oracle 11g. We'll journey through different scenarios, each accompanied by its corresponding queries, all centered around date columns in tables.
Our queries have been put to the test, so let's dive in and master the art of time-based querying!
*Note :- In toad please execute the query without (;)
CREATE TABLE orders
(
order_id NUMBER,
order_date DATE
);
SELECT *
FROM orders
WHERE Extract(month FROM order_date) = 7
AND Extract(year FROM order_date) = 2023;
SELECT *
FROM orders
WHERE To_char(order_date, 'MM') = '07'
AND To_char(order_date, 'YYYY') = '2023';
SELECT *
FROM orders
WHERE order_date BETWEEN To_date('2023-07-01', 'YYYY-MM-DD') AND
To_date('2023-07-31', 'YYYY-MM-DD');
SELECT *
FROM orders
WHERE Trunc(order_date, 'MM') = To_date('2023-07-01', 'YYYY-MM-DD')
AND Trunc(order_date, 'MM') + interval '1' month - interval '1' day =
To_date(
'2023-07-31', 'YYYY-MM-DD');
SELECT *
FROM orders
WHERE order_date >= To_date('2023-07-01', 'YYYY-MM-DD')
AND order_date < To_date('2023-08-01', 'YYYY-MM-DD');
SELECT *
FROM orders
WHERE Trunc(order_date) = To_date('2023-07-15', 'YYYY-MM-DD');
SELECT *
FROM orders
WHERE Extract(year FROM order_date) = 2023
AND Extract(month FROM order_date) = 7
AND Extract(day FROM order_date) = 15;
SELECT *
FROM orders
WHERE To_char(order_date, 'YYYY-MM-DD') = '2023-07-15';
SELECT *
FROM orders
WHERE Trunc(order_date) = To_date('2023-07-15', 'YYYY-MM-DD')
AND To_number(To_char(order_date, 'HH24')) BETWEEN 13 AND 16;
SELECT *
FROM orders
WHERE order_date >= To_date('2023-07-15 13:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND order_date < To_date('2023-07-15 16:00:00', 'YYYY-MM-DD HH24:MI:SS');
SELECT *
FROM orders
WHERE order_date >= To_date('2023-07-15 01:00:00 PM', 'YYYY-MM-DD HH:MI:SS AM')
AND order_date < To_date('2023-07-15 04:00:00 PM',
'YYYY-MM-DD HH:MI:SS AM');
SELECT To_char(order_date, 'YYYY-MM-DD HH24') AS hour_group,
Count(*) AS record_count
FROM orders
WHERE order_date >= To_date('2023-08-02 09:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND order_date < To_date('2023-08-02 19:00:00', 'YYYY-MM-DD HH24:MI:SS')
GROUP BY To_char(order_date, 'YYYY-MM-DD HH24')
ORDER BY hour_group;