Date comparison in where clause

[Guest.Visitor]

Richard Rothe wrote: > Our third-party software stores dates in some files as four numeric > fields to store century/year/month/day. > > I need to select invoices for a specific date, so I have done the > code below. When I run the statement in interactive SQL it brings the > system to its knees. CPU utilization goes ove 90%. > > Is there a better way to do this in SQL ? Richard, I don't have the commands on hand, but you might try something like this: instead of converting every record to date for comparison purposes, why not convert the Current Date to simpler values that more closely resemble the four fields? Split out the century, year, month and day values from Current Date and use them in 4 different tests. This should allow an index over these fields to be used. Bill

[dacust]

Date comparison in where clause

Even if you don't do the indexes, the technique Richard suggests can work. It you split up the current date, that operation will only be done once, instead of doing the convoluted concat once for every single record. -dan

[Absolutely_Nobody]

Date comparison in where clause

This worked much better ! Any suggestions for improvment are welcome.

---

Code

[dacust]

Date comparison in where clause

Sorry, I meant the method BILL suggested.... I'm getting lysdesic.

[coteijgeler@chello.nl]

Date comparison in where clause

I think the suggestion Bill made, is still simpler. I would do it like the following code. Regards, Carel Teijgeler

---

Code

[Absolutely_Nobody]

Date comparison in where clause

Our third-party software stores dates in some files as four numeric fields to store century/year/month/day. I need to select invoices for a specific date, so I have done the code below. When I run the statement in interactive SQL it brings the system to its knees. CPU utilization goes ove 90%. Is there a better way to do this in SQL ?

---

Code

[Guest.Visitor]

Date comparison in where clause

You're converting the original fields before comparison, which was the problem with Richard's first example. Richard's second example (based on Bill's suggestion) doesn't, and I'm guessing it would be faster.