×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help with dates

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:

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?

RE: Help with dates

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

RE: Help with dates

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

RE: Help with dates

(OP)
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.

RE: Help with dates

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close