Hi,

Can a date formatted for example (2007070400) be converted to 2007/07/04? The two zeros at the end need to be parsed out. I can't find examples on this. Thanks for any help.

Joe

Joe

yes it can....

There are many ways depending on the original format.

If it is a string (A10)

NEWYMD/A8YYMD=EDIT(ORIGINAL,'99999999');

But it also depends on the way you want to use the result. If you want to calculated with it, you should convert it to a smartdate.

There is a very good money worth book (U$ 25) that explanes all the formulas.

I believe the "Using Functions" book is also available via download for registered users, but I have a printed copy (to read in my spare time. Also serves as a nice desk decoration. I keep it in a very "reachable" spot.)

Darin

the book I referred to is the "Almost 1001 ways to work with dates" and is not in digital format available.

Thanks,

Actually, the date field in question - event_partkey is originally formatted as I11. I tried NEWYMD/I8YYMD = EDIT(EVENT_PARTKEY,'99999999'); and received a format error. Should I redefine the metadata as A10 and not as an integer.

Thanks,
Joe

Joe

do it in steps

NEWADATE/A11=EDIT(ORIGINAL); is converting from Integer to alpha.

NEWYMD/A10=EDIT(NEWADATE,'99/99/9999$$'); is masking the alpha string to the format you want, but keep in mind this is only a date looking string and not a real smart date.

If the orginal date is I11, to convert to smart date use the following in a DEFINE:

FMTDT/YYMD=DATECVT(ORIG_DT/1000,'I8YYMD','YYMD');

For example:
DEFINE FILE MACGYVER
ORIG_DT/I11=20071231000;
FMTDT/YYMD=DATECVT(ORIG_DT/1000,'I8YYMD','YYMD');
END
TABLE FILE MACGYVER
PRINT ORIG_DT FMTDT
WHERE COUNTER EQ 1
END

This seems to work as well:

 DEFINE FILE CAR
IDATE/I11 = 2007070400;
IDATE1/I8YYMD = IDATE/100;
IDATE_YYMD/YYMD = IDATE1;
END
TABLE FILE CAR 
PRINT IDATE IDATE1 IDATE_YYMD
BY COUNTRY
END 

Thanks for all your help. I finally got it to work. I did the following:

NEWADATE/A11=EDIT(EVENT_PARTKEY,'$99999999$$');
NEWYMD/A10=EDIT(NEWADATE,'9999/99/99');

Worked great!

Joe

why do you have to mask the first character?
is there a space in the database field?
is it always there?

what I would do in this case to be sure it works always is first convert the A11-string to an integer.

NEWINT/I10=EDIT(NEWADATE);

then use the integer for the next step as suggested by Piipster.

That's what baffled me too. I did not see a space in the first character until I ran test data. I'll try your suggestion and let you know how it works.

Joe