"Best" is up for question. Let's say there's an index on the year, then
month, then day field. Then, for performance reasons, you might want to
convert the select date (for example, current date) into something that
will work with those fields. However, if performance isn't that big of a
concern (small file) or if you just have to do date calculations on that
field then I'd suggest this
select date(year concat '-' concat month concat '-' concat day) as
duhDate...
providing they are character or
select date(digits(year) concat '-' concat digits(month) concat '-' concat
digits(day)) as duhDate...
if they are numeric.
Then, you have to determine you are going to handle invalid dates, etc.
So, you might wrap that up into a user defined function to do it
select MakeADate(year, month, day) as duhDate...
And WHENEVER in the UDF can determine what to return on an invalid date. A
default date or null.
Now, if you're going to be banging out queries right and left against this
file you could try creating a view.
Create view MakesSense as
(select MakeADate(year, month, day) as duhDate, ...)
And that would work fine in Query/400 even.
You might find the idate function from Alan Campin that people have been
talking about a lot lately helpful.
http://www.think400.dk/downloads.htm
Rob Berendt
midrange.com
