Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Convert Alpha Numeric to date

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Convert Alpha Numeric to date
 Login/Join
 
Platinum Member
posted
I'm having problems querying data with the alpha numeric date field. What I did was define the alpha numeric to this:
DATE1/A2=EDIT(DATE, '99$$$$$$');
DATE2/A2=EDIT(DATE, '$$99$$$$');
DATE3/A4=EDIT(DATE, '$$$$9999');
DATE4/A10=DATE1 || '/' || DATE2 || '/' || DATE3;
END

I'm using DATE4 FOR THE DATE FIELD. I keep getting for example this year and last year's data(which is in the reporting table). Is there a better way to convert this date?

Thanks,

Joe


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Guru
posted Hide Post
What is the description, ie format, of the date field in your data that you are querying?

That would help determine how to construct the appropriate search argument.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
Platinum Member
posted Hide Post
THE ALPHA FIELD IS DATE/A8


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Platinum Member
posted Hide Post
I'm sorry I should of given a better example:

A8 ex '08012008'.


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Virtuoso
posted Hide Post
This seems really simple, so I'm not sure if I understand the question. You should just be able to

NEWDATE/A8MDYY=DATE;

and if you need a smart date

NEWDATE_MDYY/MDYY=NEWDATE;

Do you need something different than that?


Regards,

Darin



In FOCUS since 1991
WF Server: 7.7.04 on Linux and Z/OS, ReportCaster, Self-Service, MRE, Java, Flex
Data: DB2/UDB, Adabas, SQL Server Output: HTML,PDF,EXL2K/07, PS, AHTML, Flex
WF Client: 77 on Linux w/Tomcat
 
Posts: 2298 | Location: Salt Lake City, Utah | Registered: February 02, 2007Report This Post
Expert
posted Hide Post
Joe,

Firstly you could have achieved your DATE4 value in one easy step -
DATE4/A10=EDIT(DATE, '99/99/9999');


One thing to remember about alpha representations of date fields (A8 not A8DMYY etc.) is that they are not dates, they only "look" like dates and won't be parsed like dates. If you are going to compare like for like in an equality test then you really do not have to bother too much about how you represent the value e.g. if you use WHERE date1 EQ date2.

However, if you are going to do a range evaluation then you really ought to rearrange the alpha field to look like year month day or use a proper date field (if you can) as then a comparison such as WHERE date1 FROM date2 TO date3 will work so much better. For example, using the above test, if you have date2 and date3 values of "01012008" and "31122008" you will retrieve date1 values of "01022007" and "01022009" because they fall within the range given. If you were to reverse the format and use a YYMD look alike then using the same test as above you would only get data that looked like dates from 2008.

Finally, you would get better results if you created DATE4 as an Alpha Date as opposed to a straight Alpha field (A8DMYY instead of A8)!

I would suggest that your code would need to be like this - DATE_TEST//DMYY = DATECVT(DATE,'A8DMYY','DMYY');

Use this to see what I mean. First copy and paste the code into a fex and run it as is. You will see that it returns "dates" outside the imagined selection. Then change the where test to WHERE DATE_TEST2 FROM etc. and see that you now only get the "date" range that you expected. The first part of the code just creates a temporary file to mimic the type of data you inferred that you have.

EX -LINES 3 EDAPUT MASTER,JOESDATES,CF,MEM,FILENAME=JOESDATES, SUFFIX=XFOC,$
SEGNAME=SEG1
  FIELD=DATE, ,A8 ,A8 , $
-RUN
CREATE FILE JOESDATES
MODIFY FILE JOESDATES
FIXFORM DATE.A8.
LOG FORMAT MSG OFF
LOG TRANS MSG OFF
LOG INVALID MSG OFF
DATA
-SET &Year = 2007;
-SET &Day   = 1;
-SET &Month = 1;
-SET &Yearx = &Year;
-REPEAT :Loop1 3 TIMES;
-REPEAT :Loop2 12 TIMES;
-REPEAT :Loop3 31 TIMES;
-SET &Dayx = IF &Day LT 10 THEN '0' || &Day ELSE &Day;
-SET &Mnth = IF &Month  LT 10 THEN '0' || &Month ELSE &Month;
&Dayx&Mnth&Yearx
-SET &Day = &Day + 1;
-:Loop3
-SET &Day = 1;
-SET &Month = &Month + 1;
-:Loop2
-SET &Day = 1;
-SET &Month = 1;
-SET &Yearx = &Yearx + 1;
-:Loop1
END
-RUN
DEFINE FILE JOESDATES
  DATE_TEST1/A10  = EDIT(DATE,'99/99/9999');
  DATE_TEST2/DMYY = DATECVT(DATE,'A8DMYY','DMYY');
END
TABLE FILE JOESDATES
PRINT * DATE_TEST1 DATE_TEST2
-* Change DATE_TEST1 to DATE_TEST2 and then rerun
WHERE DATE_TEST1 FROM '01/01/2008' TO '31/12/2008'
END

Good luck

T

This message has been edited. Last edited by: Tony A,



In FOCUS
since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2  
WebFOCUS App Studio 8.2.06 standalone on Windows 10 
 
Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004Report This Post
Platinum Member
posted Hide Post
Thanks for all of your suggestions. The smart date define worked! I thought I tried that. My format had mddyy instead of mdyy.

Thanks,

Joe


WebFocus 7.7.02 WinXP
 
Posts: 236 | Registered: May 12, 2006Report This Post
Virtuoso
posted Hide Post
Joe

There is a wonderful book that you should buy
It does give you all kind of tricks with dates. Converting, calculating and lot more.

the name is "Almost 1001 ways to work with dates"




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, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Convert Alpha Numeric to date

Copyright © 1996-2020 Information Builders