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

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.

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.

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,

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

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

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...