Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Current Date Calculation
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Current Date Calculation
 Login/Join
 
Member
posted
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
 
Posts: 12 | Registered: August 26, 2010Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 12 | Registered: August 26, 2010Reply With QuoteReport This Post
Guru
posted Hide Post
Frank-
You beat me to it!


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 12 | Registered: August 26, 2010Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 12 | Registered: August 26, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: October 14, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
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.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6136 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: October 14, 2010Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 2723 | Location: Ann Arbor, MI | Registered: April 05, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Guru
posted Hide Post
-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
 
Posts: 294 | Registered: March 04, 2010Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 6 | Registered: October 14, 2010Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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
 
Posts: 221 | Location: Lincoln Nebraska | Registered: August 12, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
FYI, this is the topic that Jim is referring to: http://forums.informationbuild...71057331/m/150108155


Kerry Zhan
Focal Point Moderator
Information Builders, Inc.
 
Posts: 1950 | Location: New York | Registered: November 16, 2004Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Current Date Calculation

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.