how to make selections on dates in SQL when coded as numbers
I'm trying, qithout success, to achieve the following.
Usually, I'm solving this kind of issue by means of an ApplyingMap, but I'm not sure it is the optimal solution.
I have a field which is including all the incoming quantities.
I then would like to include a field which is considering just the quantities that are incoming in the next two weeks AND that are coming by truck, regardless if within two weeks or one month time. All these quantities need to be in the same field.
The source is AS400 and the dates are coded as 20121210.
I think that the SQL date-related functions are not working properly because those 'dates' are coded as numbers (I am getting no results).
How would you tackle this problem? Importing the AS400 source in QV first, by using a Date#, and then doing some IF functions regarding the dates needed and whether shipment is by truck?
Re: how to make selections on dates in SQL when coded as numbers
thank you for your answer.
I cannot use the date#(date'YYYYMMDD') function, or better, I can use it, but is not effective.
By using that function, I would fist need to load all the data, interpret the dates, so that then I can filter on the two weeks.
My goal is to have the filtering directly from the source, so, in the SQL command, to have a WHERE statement, which is picking up just the dates of my interests. but yeah, problem is that those datesin SQL are numbers.
I was trying some SQL command like ADD date or so (did not save them), but they were not leading to anywhere.
So in the end, to save time (in design phase, for sure not in reload timings) I had to rely on the "easy" way, i.e. importing and then filtering from the RESIDENT table.
but if a proper way of filtering the weeks directly from SQL source, where the field is a number, woudl be much appreciated, tho I'm aware the script might result quite complex, so maybe all in all is not worth the trouble...