Help with dates

[celauritz]

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:

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?

 

[r937]

efficiency will never happen, you'll always get a table scan, and you can thank the database designer for that happy state of affairs

which database system is it? msaccess?



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[Thargy]

Yes,

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

 

[celauritz]

I figured as much. The problem with the database is that it is read-only on my end, but I am the person that is asked to report off of it, though I am not allowed to make any changes without request of the vendor.

I figured I was stuck.

Thanks for replying.

 

[Thargy]

That said,

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