Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] alpha conversion to datetime
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] alpha conversion to datetime
 Login/Join
 
Member
posted
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
 
Posts: 13 | Registered: November 29, 2011Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Reply With QuoteReport This Post
Expert
posted Hide Post
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
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 13 | Registered: November 29, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Share your code


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2165 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
Member
posted Hide Post
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
 
Posts: 13 | Registered: November 29, 2011Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Change the subject of your first post by adding [SOLVED]


WF versions : Prod 8.2.0.1M gen 240, Dev 8.2.04 gen 48, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2165 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] alpha conversion to datetime

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.