I'm having problems querying data with the alpha numeric date field. What I did was define the alpha numeric to this:
DATE1/A2=EDIT(DATE, '99$$$$$$');
DATE2/A2=EDIT(DATE, '$$99$$$$');
DATE3/A4=EDIT(DATE, '$$$$9999');
DATE4/A10=DATE1 || '/' || DATE2 || '/' || DATE3;
END

I'm using DATE4 FOR THE DATE FIELD. I keep getting for example this year and last year's data(which is in the reporting table). Is there a better way to convert this date?

Thanks,

Joe

What is the description, ie format, of the date field in your data that you are querying?

That would help determine how to construct the appropriate search argument.

THE ALPHA FIELD IS DATE/A8

I'm sorry I should of given a better example:

A8 ex '08012008'.

This seems really simple, so I'm not sure if I understand the question. You should just be able to

NEWDATE/A8MDYY=DATE;

and if you need a smart date

NEWDATE_MDYY/MDYY=NEWDATE;

Do you need something different than that?

Joe,

Firstly you could have achieved your DATE4 value in one easy step -

DATE4/A10=EDIT(DATE, '99/99/9999');

One thing to remember about alpha representations of date fields (A8 not A8DMYY etc.) is that they are not dates, they only "look" like dates and won't be parsed like dates. If you are going to compare like for like in an equality test then you really do not have to bother too much about how you represent the value e.g. if you use WHERE date1 EQ date2.

However, if you are going to do a range evaluation then you really ought to rearrange the alpha field to look like year month day or use a proper date field (if you can) as then a comparison such as WHERE date1 FROM date2 TO date3 will work so much better. For example, using the above test, if you have date2 and date3 values of "01012008" and "31122008" you will retrieve date1 values of "01022007" and "01022009" because they fall within the range given. If you were to reverse the format and use a YYMD look alike then using the same test as above you would only get data that looked like dates from 2008.

Finally, you would get better results if you created DATE4 as an Alpha Date as opposed to a straight Alpha field (A8DMYY instead of A8)!

I would suggest that your code would need to be like this - DATE_TEST//DMYY = DATECVT(DATE,'A8DMYY','DMYY');

Use this to see what I mean. First copy and paste the code into a fex and run it as is. You will see that it returns "dates" outside the imagined selection. Then change the where test to WHERE DATE_TEST2 FROM etc. and see that you now only get the "date" range that you expected. The first part of the code just creates a temporary file to mimic the type of data you inferred that you have.

EX -LINES 3 EDAPUT MASTER,JOESDATES,CF,MEM,FILENAME=JOESDATES, SUFFIX=XFOC,$
SEGNAME=SEG1
  FIELD=DATE, ,A8 ,A8 , $
-RUN
CREATE FILE JOESDATES
MODIFY FILE JOESDATES
FIXFORM DATE.A8.
LOG FORMAT MSG OFF
LOG TRANS MSG OFF
LOG INVALID MSG OFF
DATA
-SET &Year = 2007;
-SET &Day   = 1;
-SET &Month = 1;
-SET &Yearx = &Year;
-REPEAT :Loop1 3 TIMES;
-REPEAT :Loop2 12 TIMES;
-REPEAT :Loop3 31 TIMES;
-SET &Dayx = IF &Day LT 10 THEN '0' || &Day ELSE &Day;
-SET &Mnth = IF &Month  LT 10 THEN '0' || &Month ELSE &Month;
&Dayx&Mnth&Yearx
-SET &Day = &Day + 1;
-:Loop3
-SET &Day = 1;
-SET &Month = &Month + 1;
-:Loop2
-SET &Day = 1;
-SET &Month = 1;
-SET &Yearx = &Yearx + 1;
-:Loop1
END
-RUN
DEFINE FILE JOESDATES
  DATE_TEST1/A10  = EDIT(DATE,'99/99/9999');
  DATE_TEST2/DMYY = DATECVT(DATE,'A8DMYY','DMYY');
END
TABLE FILE JOESDATES
PRINT * DATE_TEST1 DATE_TEST2
-* Change DATE_TEST1 to DATE_TEST2 and then rerun
WHERE DATE_TEST1 FROM '01/01/2008' TO '31/12/2008'
END

Good luck

T

This message has been edited. Last edited by: Tony A,

Thanks for all of your suggestions. The smart date define worked! I thought I tried that. My format had mddyy instead of mdyy.

Thanks,

Joe

Joe

There is a wonderful book that you should buy
It does give you all kind of tricks with dates. Converting, calculating and lot more.

the name is "Almost 1001 ways to work with dates"