I have a database that, for every single date within, breaks out century, year, month and day. So, in the century field in, say, the orders table, today would be 20, the year would be 11, the month would be 1 and the day would be 21. Thus, whenever I have to report on date ranges, I first have to put the date together and then put a query within a query like so:
Is there a better (or more efficient) way to do this?
Code:
SELECT
o.order_no
FROM
(
SELECT
date(orders.month||'/'||orders.day||'/'||((orders.century*100)+orders.year)) as order_date,
order_no
FROM orders
)o
WHERE order_date >= date(12||'/'||15||'/'||2010) and order_date <= date(1||'/'||15||'/'||2011)Is there a better (or more efficient) way to do this?