Help with dates
Help with dates
(OP)
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)
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?
Talk To Other Members
RE: Help with dates
which database system is it? msaccess?
r937.com | rudy.ca
Buy my new book Simply SQL from Amazon
RE: Help with dates
the only way to achieve efficiency with dates is to store them in a date field. To do this you should:-
Add fields whose data type is date, one for each existing date field.
Update the new fields to be the dates from the poorly (or indeed diabolically badly) constructed dates, and commit the change.
Then drop the original date disaster fields.
Then you may query using the RDBMS date functions.
Regards
T
RE: Help with dates
I figured I was stuck.
Thanks for replying.
RE: Help with dates
you now have a business case for making the idiots in charge of the db do something about it.
Alternatively, you can copy all of their dross locally, put it into date fields in your local copy, and then report swiftly. You could schedule an overnight batch job to refresh your local copy.
Regards
T