Focal Point
[SOLVED] Current Date Calculation

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/150108155

October 14, 2010, 03:25 PM
Dennis Parish
[SOLVED] Current Date Calculation
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 Parish

WebFOCUS 7.6
Windows, All Outputs
October 14, 2010, 03:41 PM
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

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

October 14, 2010, 03:43 PM
Dennis Parish
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



Dennis Parish

WebFOCUS 7.6
Windows, All Outputs
October 14, 2010, 03:43 PM
jimster06
Frank-
You beat me to it!


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
October 14, 2010, 03:49 PM
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.




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

October 14, 2010, 03:49 PM
Dennis Parish
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.


Dennis Parish

WebFOCUS 7.6
Windows, All Outputs
October 14, 2010, 03:54 PM
Dennis Parish
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.



Dennis Parish

WebFOCUS 7.6
Windows, All Outputs
October 14, 2010, 04:05 PM
jfr99
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


WebFocus 8.201M, Windows, App Studio
October 14, 2010, 04:17 PM
Waz
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 ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

October 15, 2010, 07:57 AM
Hayley Gowan
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.


WebFOCUS 7.6
Windows, All Outputs
October 15, 2010, 09:44 AM
GinnyJakes
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
October 17, 2010, 04:39 PM
Waz
You could use HGETC with Ginny' suggestion, this will get the current datetime.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

October 19, 2010, 08:12 AM
Hayley Gowan
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?


WebFOCUS 7.6
Windows, All Outputs
October 19, 2010, 09:13 AM
GinnyJakes
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.


Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
October 19, 2010, 09:53 AM
jfr99
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


WebFocus 8.201M, Windows, App Studio
October 19, 2010, 10:04 PM
Arif
-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.


WebFOCUS 7.6.10
Windows
HTML
October 20, 2010, 08:56 AM
Hayley Gowan
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 *


WebFOCUS 7.6
Windows, All Outputs
October 20, 2010, 12:23 PM
jfr99
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


WebFocus 8.201M, Windows, App Studio
October 25, 2010, 02:25 PM
Kerry
FYI, this is the topic that Jim is referring to: http://forums.informationbuild...71057331/m/150108155


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.