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     Coverting Character Fields to a Date

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Coverting Character Fields to a Date
 Login/Join
 
gcarelse
Member
posted
Hello,

I have CHAR fields in an Oracle database and I would like to convert them to a date in Web Focus 7.6.1. I tried:

NEWDATE/YY = HINPUT(4,YEAR,8,'YY');

where YEAR is the name of my column in Oracle, which is 4 characters long. When I run my report I get the following error:

INVALID TYPE OF ARGUMENT #4 FOR USER FUNCTION HINPUT

Is there another function that can convert 4 character year values and 2 character month values (eg.08) to dates?


Regards,


Grant


Grant
Oracle 9, Web Focus Version 7.6.1
 
Posts: 19 | Registered: August 02, 2007Report This Post
jimster06
Guru
posted Hide Post
The HINPUT function usually converts an alpha date the 'STANDARD' date format; the fourth parm would be in H format such as HYYMDs. If you are converting a date to standard format, I would think that you would have a day to go with the month and year...right?

In the upper right hand of this screen there is a link to the Technical Documentation Library which can be very helpful.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
 
Posts: 252 | Location: USA | Registered: April 15, 2003Report This Post
FrankDutch
Virtuoso
posted Hide Post
What is the value of your character field?

Is it something like 'yyyymmdd' or 'yyyy/mm/dd'?

do you need to convert it to a smartdate?
is the result for calculations (number of days etc) or just for display in a report.




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
Tom Flynn
Expert
posted Hide Post
For YEAR

DEFINE FILE CAR
NU_YYM1/I4YY WITH COUNTRY = EDIT(YEAR);
NEW_DATE/YY WITH COUNTRY = NU_YYM1;
END
TABLE FILE CAR
PRINT
NU_YYM1
NEW_DATE
BY COUNTRY
END
-EXIT

For YEAR, MONTH

DEFINE FILE CAR
NU_YYM1/A6 WITH COUNTRY = YEAR | MONTH;
NU_YYM2/I6YYM WITH COUNTRY = EDIT(NU_YYM1);
NEW_DATE/YYM WITH COUNTRY = NU_YYM2;
END
TABLE FILE CAR
PRINT
NU_YYM1
NU_YYM2
NEW_DATE
BY COUNTRY
END
-EXIT

This message has been edited. Last edited by: Tom Flynn,


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1972 | Location: Centennial, CO | Registered: January 31, 2006Report This Post
<dksib>
posted
quote:
NEWDATE/YY = HINPUT(4,YEAR,8,'YY');


This does work in 7.1.1 webfocus using an Oracle database. If you need this to be in the SQL for Oracle please combind the two date fields and add '01' for the day so that you can use the TO_DATE and the TO_Char to create the date field you are looking for.

If not to keep it simple:
DEFINE FILE CAR
NEWDT/A6YYM = Year|| Month;
NEWDATE/YYMD =NEWDT;
END

You do not need the D(day) but it makes it user to understand later when you compare it to other date fields.

David
 
Report This Post
gcarelse
Member
posted Hide Post
quote:
NU_YYM1/I4YY WITH COUNTRY = EDIT(YEAR);
NEW_DATE/YY WITH COUNTRY = NU_YYM1;

Hello,

Thanks Tom. I used your following code to get the results I needed (modified a bit):

NU_YYM1/I4YY WITH COUNTRY = EDIT(YEAR);
NEW_DATE/YY WITH COUNTRY = NU_YYM1;

NU_YYM1/A6 WITH COUNTRY = YEAR | MONTH;
NU_YYM2/I6YYM WITH COUNTRY = EDIT(NU_YYM1);
NEW_DATE/YYM WITH COUNTRY = NU_YYM2;

Could you explain what I6YYM is? Thanks.


Regards,


Grant


Grant
Oracle 9, Web Focus Version 7.6.1
 
Posts: 19 | Registered: August 02, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
Grant

the format I6YYM stands for a number 6 positions the first two are the century, the next two the year and the last two the month so

YYM=199704 : april 1994 or
YYM=200711 : november 2007

There is also a I8YYMD...you might understand that the last two figures are for the day.

There is also A8YYMD that is more or less the same but now in alpha format (although you only will see numbers).
These formats only look like dates. You can use them to display but not to calculate.

If you want to calc the number of days between the fields:
BEGINDATE/A8YYMD='20060417';
ENDDATE/I8YYMD=20070922;

You first have to convert them to smartdates and you can.
It is to much to explain all about this, you can buy a book that does it ($ 25).

btw would you be so kind to upgrade your signature...




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     Coverting Character Fields to a Date

Copyright © 1996-2020 Information Builders