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.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
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.
You could use HGETC with Ginny' suggestion, this will get the current datetime.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
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.
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.
&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