equivalent for Between function

ragu · Post by **ragu** » Mon Dec 12, 2005 3:02 am

Hi Everybody,

I need the equivalent for date between '01-Jan-2004' and '31-jan-2005'
I am trying to select data that fall between a particular date
I used ICONV and OCONV to convert the dates to the required format
I used this in the constraint

stageVariable1 >= StageVariable2 AND stageVariable1 <= stageVariable3

I am getting the data that satisfy the first condition and not the second condition
then i tried
(stageVariable1 >= StageVariable2 AND stageVariable1 <= stageVariable3)

This also gave the same result

Can somebody help me with this?

Thanks

Ragu

ArndW · Post by **ArndW** » Mon Dec 12, 2005 3:38 am

Can you give us exact values for your 3 variables? Put an output link to a dummy text file with the 3 columns, plus you can also add 2 extra columns containing your derivations "stageVariable1 >= StageVariable2" and also "stageVariable1 <= stageVariable3"; these will have the value of either 0 for False and 1 for true. That should help you debug your logic.

kcbland · Post by **kcbland** » Mon Dec 12, 2005 6:26 am

'01-Jan-2004' is text so it's a left to right comparison. You're attempting to do a DATE data type comparison, but because DS does string compares in most situations, you'll never get the right answer. If you're comparing dates put them into YYYY-MM-DD format. This will work with every comparison, string or numeric.

ragu · Post by **ragu** » Mon Dec 12, 2005 11:43 am

Thank for the response,

The dates are in MDY format
Fieldx = 01-19-2005
SV2 = 01-01-1997
Filedy = 07-31-2001

(SV1 >= Fieldx and SV1 <= Fieldy)

I think this might be the problem
I am using the right hand side values directly from the Hash file
I am not storing it in a Stage Variable
Does that make a difference?

Thanks
Ragu

ArndW · Post by **ArndW** » Mon Dec 12, 2005 11:46 am

ragu,

yes it makes a difference. In the original post you stated that you had used ICONV/OCONV to format the data so I assumed that you were doing a numeric comparison on the internal date. If you are doing a string comparison on the date you need to get both formats in the form YYYY-MM-DD. Just make sure that you are comparing like types and not a string format on the left and a numeric internal format on the right.

ragu · Post by **ragu** » Mon Dec 12, 2005 12:01 pm

Hi Arnd,

Thanks
I used ICONV/OCONV while generating the Hash file
I assumed it will be OK
As a matter of fact, I have done exactly the same in a previous job and it worked well
I will change and see how this works

Ragu

kcbland · Post by **kcbland** » Mon Dec 12, 2005 12:14 pm

To clarify, I assumed you used ICONV to internalize, and then OCONV to externalize, but used the externalized value in the expressions. To get integer comparisons, the ICONV returned value would have worked.

It's probably easiest to always work in YYYY-MM-DD format, rather than internalize just for comparisons. If you are doing date math, you need to use the internalized values so that you get integer days.

ragu · Post by **ragu** » Mon Dec 12, 2005 12:27 pm

Thanks,

I just thought it will be better just to use ICONV and use the internal value to do the comparison

I will try that and hope it solves the problem

Thanks
Ragu

ragu · Post by **ragu** » Mon Dec 12, 2005 6:44 pm

Thanks everybody,

i used ICONV alone and did the internal conversion alone in the stage variable and got the job done

Thanks
Ragu