Focal Point
[SOLVED] alpha conversion to datetime

This topic can be found at:
http://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/2517063576

March 30, 2015, 03:52 PM
jessicne
[SOLVED] alpha conversion to datetime
Is it possible to convert from an alpha in the format mm/dd/yy hh:mm:ss to a datetime that includes all the time information in a define?

This message has been edited. Last edited by: <Kathryn Henning>,


WebFocus 8.0.0.7
Window XP SP3
Excel, PDF, HTML
March 30, 2015, 05:14 PM
Francis Mariani
It most probably is possible.

Here is an example fex.

MY_ALPHA_DATE_TIME1 is the alpha column containing the date-time data.
TEMP_ALPHA_DATE_TIME1 strips the date-time separators and adds the century.
TEMP_DATE_TIME1 is the converted date-time column in YYYY/MM/DD HH:MM:SS format
DEF_DATE_TIME1 is the converted date-time column in the desired format.

For some reason, using MM/DD/YYYY dates with HINPUT does not seem to work, see TEMP_ALPHA_DATE_TIME2 and DEF_DATE_TIME2.

To avoid problems, I always use dates in YYYY/MM/DD format.

-SET &ECHO='ON';

DEFINE FILE CAR
MY_ALPHA_DATE_TIME1/A23 WITH MODEL = '03/30/15 16:25:05';

TEMP_ALPHA_DATE_TIME1/A14 = '20' || EDIT(MY_ALPHA_DATE_TIME1, '$$$$$$99')
                                 || EDIT(MY_ALPHA_DATE_TIME1, '99') 
                                 || EDIT(MY_ALPHA_DATE_TIME1, '$$$99') 
                                 || EDIT(MY_ALPHA_DATE_TIME1, '$$$$$$$$$99$99$99');
TEMP_DATE_TIME1/HYYMDS    = HINPUT(14, TEMP_ALPHA_DATE_TIME1, 8, 'HYYMDS');
DEF_DATE_TIME1/HMDYS      = TEMP_DATE_TIME1;

TEMP_ALPHA_DATE_TIME2/A14 = EDIT(MY_ALPHA_DATE_TIME1, '99$99$2099$99$99$99');
DEF_DATE_TIME2/HMDYYS     = HINPUT(14, TEMP_ALPHA_DATE_TIME2, 8, 'HMDYYS');
END

TABLE FILE CAR
PRINT
MY_ALPHA_DATE_TIME1
TEMP_ALPHA_DATE_TIME1
TEMP_DATE_TIME1
DEF_DATE_TIME1

TEMP_ALPHA_DATE_TIME2
DEF_DATE_TIME2

WHERE RECORDLIMIT EQ 1
END
-RUN



Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 30, 2015, 05:43 PM
Alan B
Straight into HINPUT works for me...
SET DEFCENT=19, YRTHRESH=20
DEFINE FILE CAR
A_DATE/A17  = '03/30/15 22:34:27';
D_DATE/HYYMDS = HINPUT(17,A_DATE,8,'HMDYS');
END
TABLE FILE CAR
PRINT D_DATE
BY COUNTRY
END



Alan.
WF 7.705/8.007
March 30, 2015, 07:27 PM
Francis Mariani
Alan, yes it does! I don't know why I thought I needed to rearrange the date components - all my example code stripped the slashes and colons. Thanks for the tip.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
March 31, 2015, 09:27 AM
jessicne
Thank you both for your help.

When I tried both examples, I couldn't get the time information to stay after using the HINPUT. It would just give me a date and the time information was missing.


WebFocus 8.0.0.7
Window XP SP3
Excel, PDF, HTML
March 31, 2015, 09:30 AM
MartinY
Share your code


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
March 31, 2015, 09:36 AM
jessicne
Nevermind my last post - I had the syntax wrong. It worked. Thank you both for your help!


WebFocus 8.0.0.7
Window XP SP3
Excel, PDF, HTML
March 31, 2015, 10:00 AM
MartinY
Change the subject of your first post by adding [SOLVED]


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007