Hello, I've read thru the posts but cannot find a solution to my problem.

I wish to subtract a date field (MDYY)from the current date to obtain a past due value. I've tried creating a define using TODAY but am only permitted to create it as an alpanumeric field which I am unable to substract from my date field. I've tried using the format function to change the TODAY field to MDYY but that is not sucessful either.

Is there a way to do this?

This message has been edited. Last edited by: Kerry,

Dennis

Ever thought of buying that famous book?

In the help function of the formulas there is a lot that might help you.
Is the datefield in a database and is it a smartdate or a string?
The current date is &YYMD it will give you something like 20101014.
So you can do a define.

DEFINE FILE XXX
ACTUAL/YYMD=&YYMD;
...

search for DATEDIF

What book?

quote:

Originally posted by FrankDutch:
Dennis

Ever thought of buying that famous book?

In the help function of the formulas there is a lot that might help you.
Is the datefield in a database and is it a smartdate or a string?
The current date is &YYMD it will give you something like 20101014.
So you can do a define.

DEFINE FILE XXX
ACTUAL/YYMD=&YYMD;
...

search for DATEDIF

Frank-
You beat me to it!

it might be a smart idea for you to buy a nice book called "(Almost) 1001 Ways to Work with DATES in WebFOCUS" ISBN 978-0-9791722-1-2
It cost maybe 25 U$ and is worth all that dollars.

I'm not sure I understand your DATEDIF solution. My documentation indicates:

fromYYMD, toYYMD, unit

and as I said earlier TODAY can only be alphanumeric and results in and error when a YYMD field is subtracted from it.

Thanks, I will do that.

quote:

Originally posted by FrankDutch:
it might be a smart idea for you to buy a nice book called "(Almost) 1001 Ways to Work with DATES in WebFOCUS" ISBN 978-0-9791722-1-2
It cost maybe 25 U$ and is worth all that dollars.

Here's an example using the CAR file. This changes the dates to "Smart Dates" and then just subtracts the dates.

-*
DEFINE FILE CAR
CNT/I3 WITH COUNTRY = CNT + 1;
-*
I8_DT/I8YYMD = DECODE CNT (
1 20101001
2 20101010
3 20101101
4 20100101
5 20090101
ELSE 20000101);
END
-*
TABLE FILE CAR
PRINT
CNT
I8_DT
WHERE RECORDLIMIT EQ 5
ON TABLE HOLD
END
-*
DEFINE FILE HOLD
I8_TODAY/I8YYMD = &YYMD;
SD_TODAY/MDYY = I8_TODAY;
SD_DT/MDYY = I8_DT;
DIFF/I7 = SD_TODAY - SD_DT;
END
-*
TABLE FILE HOLD
PRINT
I8_TODAY
SD_TODAY
CNT
I8_DT
SD_DT
DIFF
END

Maybe this will help.

Jim

As Frank alluded to, you can put the current date into a smart date directly.

Then things become very simple.

As a smart date (formats like YYMD, MDYY, DMYY, etc) are essentially stored as number of days since a base date. Therefore once you have two dates in this format, one can be subtracted from the other.

Easy.

The key question is, what format is your date from the database ?

This is related in a way to your current day topic. As I need to create a hold file of randomly retrieved records, I thought I could use the lowest microseconds position in the timestamp as part of some formula to do it.

Is there some way I can access the current timestamp, convert it to A26 and parse out that final last position value? I have tried the HYYMDm function --- only with CAPS OFF in my profile --- and I can only get the smart date back, but I can't convert it successfully.

BUT, if there is a better way to get the hold file of randomly selected recods, please let me know. I have a deadline of 2 weeks for the Excel file output.

Have you looked at the HCNVRT function? Not sure what the format of your input field is. Maybe you can provide that information so that we can better help you.

You could use HGETC with Ginny' suggestion, this will get the current datetime.

Thank you, but I had already used your suggestion in the function as "DT2/HYMMDm = HGETC(10, 'HYMMDm');" to display the timestamp. (I just did it again as 2010/10/19 08:04:43.385986).

Printing it is good to know. However, here I need to get the last value in the mircoseconds -- in this case, '6'. How do I code the series of statements that will convert this smart date into text it for me?

quote:

The HCNVRT function converts a date-time value to alphanumeric format for use with operators
such as EDIT, CONTAINS, and LIKE.

This line of documentation is in the Using Functions manual. If you convert your datetime stamp to alphanumeric using this subroutine, you can then use EDIT on the result to extract the character that you need.

Hi Hayley,

Here is an example for getting the 26th position of the datetime stamp:

-*
DT2/HYYMDm = HGETC(10, 'HYYMDm');
DT2A/A26 = HCNVRT(DT2, '(HYYMDm)', 26, 'A26');
DT2A_P26/A1 = SUBSTR(26, DT2A, 26, 26, 1, 'A1');
-*

In looking at your first post.......here is an example of printing 3 random cars from the CAR file using a random number function. Don't know if this will be of any use, but here it is:

-*
DEFINE FILE CAR
CAR_RAND/D12.8 WITH CAR = RDUNIF(CAR_RAND);
END
-*
TABLE FILE CAR
PRINT CAR
BY CAR_RAND
ON TABLE HOLD
END
-*
TABLE FILE HOLD
PRINT *
WHERE RECORDLIMIT EQ 3
END

I've used this method to pull a random subset from a file and it has worked for me.

Jim

-SET &DATER = DATECVT((DATEADD((DATECVT(&YYMD,'I8YYMD','YYMD')), 'D', -1)), 'YYMD','I8YYMD');

&DATER VARIABLENAME
DATECVT date convert function
DATEADD is date manipulation function to - days
'D' Day
-1 is only one days being subratcted to get yesterday you can subratct however many days you want.

Thank you, Jim.
Using the RDUNIF function worked perfectly for me, too. As expected, I got different results from the 2 consecutive times I ran the test batch query. And since all I need is to do get one set of randomly selected projects one time within the program, this is fine.

However, there was one detail that needs to be shared for anyone else whose specs might not be the same. If I ran the function twice in the same program and using 2 different defined random number names, I got the SAME results where I would have expected an entirely different set with the different function names.

This is my base file.
---- ----
PROJ NAME
---- ----
28 PROJECT 28
49 PROJECT 49
63 PROJECT 63
74 PROJECT 74
321 PROJECT 321
502 PROJECT 502

FYI: RAND_01/D12.8 = RDUNIF(RAND_01);

DISPLAY RANDOM FILE 1
RAND_01 PROJ NAME
------- ---- ----
.16259415 28 PROJECT 28
.21508715 49 PROJECT 49
.24673269 502 PROJECT 502
.30672254 321 PROJECT 321
.69007673 63 PROJECT 63
.98673983 74 PROJECT 74

FYI: RAND_02/D12.8 = RDUNIF(RAND_02);

DISPLAY RANDOM FILE 2
RAND_02 PROJ NAME
------- ---- ----
.16259415 28 PROJECT 28
.21508715 49 PROJECT 49
.24673269 502 PROJECT 502
.30672254 321 PROJECT 321
.69007673 63 PROJECT 63
.98673983 74 PROJECT 74
*************************** Bottom of Data *

Hi Hayley,

I created a new thread with a subject = How to create a random subset of a file using functions RDUNIF or PRDUNI.

Please refer to that for further discussion.

Jim

FYI, this is the topic that Jim is referring to: http://forums.informationbuild...71057331/m/150108155