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] convert character date of mm/dd/yyy format to date format of mm/dd/yyyy
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] convert character date of mm/dd/yyy format to date format of mm/dd/yyyy
 Login/Join
 
Gold member
posted
I have a master file date in char (A20V) format of mm/dd/yyy. I need to convert it to yymmdd so I can select MIN date.

I tried this (one of many trys)

DEFINE FILE WKFLOW_DOC_TYPE_FACT

OldEndDate/A20=CHGDAT('MDYY', 'YYMD',WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DISPLAYMMYY, 'A8');
END

And when I use is it is blank..
Any help is greatly appreciated

This message has been edited. Last edited by: Kerry,



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
mm/dd/yyy

More details please: Does it contain slashes? It's not really yyy, is it?


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
Expert
posted Hide Post
quote:
mm/dd/yyy


How is the date formatted ?

Do you have an example ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Gold member
posted Hide Post
Sorry, got carried with my y's

It is formated st follows:
09/29/2011



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Expert
posted Hide Post
So, just to be sure, that A20V column has data that contains the slashes?

If so, try the following:
 
TABLE FILE CAR
PRINT
COMPUTE DISPLAYMMYY/A20V = '09/29/2011';
COMPUTE OLDENDDATE1/A8MDYY = EDIT(DISPLAYMMYY,'99$99$9999');
OLDENDDATE/YYMD = OLDENDDATE1;
BY COUNTRY
END


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
Gold member
posted Hide Post
Why not just do the conversion in the master?

FIELDNAME=VDATE, ALIAS=E02, USAGE=MDYY, ACTUAL=A20V, DATEPATTERN='[MM]/[DD]/[YYYY]', $

Having the DATEPATTERN will allow you to set the usage to what ever you need: YYMD, MtrDYY, etc.



Windows: WF 7.6.2: SQL Server 2008 R2
 
Posts: 86 | Location: Chicago | Registered: August 03, 2007Reply With QuoteReport This Post
Gold member
posted Hide Post
I tried your example and When I clcked the run button it doesn't run, there are no errors. I also found that there are some records that are mm/dd (01/23).

My code below, maybe I am missing something?

TABLE FILE WKFLOW_DOC_TYPE_FACT
COMPUTE DISPLAYMMYY/A20V = '09/29/2011';
COMPUTE OLDENDDATE1/A8MDYY = EDIT(DISPLAYMMYY,'99$99$9999');
OLDENDDATE/YYMD = OLDENDDATE1;
SUM
WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.OLDENDDATE
J3.TRAN_TYPE_DIM.TRAN_TYPE_NM
WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DISPLAYMMYY
WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.ACTIVE
WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.PEND
BY LOWEST J0.USER_DIM.USER_ID
BY LOWEST J2.NODE_DIM.NODE_NM
BY LOWEST WKFLOW_DOC_TYPE_FACT.WKFLOW_DOC_TYPE_FACT.DISPLAYMMYY NOPRINT
WHERE J3.TRAN_TYPE_DIM.TRAN_TYPE_CD EQ 'E';
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N1,
SQUEEZE=1.680556,
$
TYPE=REPORT,
COLUMN=N2,
SQUEEZE=1.430556,
$
ENDSTYLE
END



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
Got it resolved. Put it in a define and date is converted.

Thx all for you help Smiler



WebFOCUS 7.7.03
Windows 7
 
Posts: 55 | Location: USA | Registered: November 02, 2009Reply With QuoteReport This Post
Gold member
posted Hide Post
Brenda,

quote:

TABLE FILE WKFLOW_DOC_TYPE_FACT
COMPUTE DISPLAYMMYY/A20V = '09/29/2011';
COMPUTE OLDENDDATE1/A8MDYY = EDIT(DISPLAYMMYY,'99$99$9999');
OLDENDDATE/YYMD = OLDENDDATE1;
SUM



You have the COMPUTEs BEFORE the verb (SUM). Move the computes AFTER the SUM or add another verb (SUM) before the computes.


WF 7.6.10, Windows, PDF, Excel
 
Posts: 75 | Location: Dallas, TX | Registered: February 12, 2004Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
DATEPATTERN


Very interesting, and quite useful.

Good One


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
DATEPATTERN

Yes, that is interesting - it must be v7.7...


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
Expert
posted Hide Post
Docco says 7.6


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6270 | Location: 33°49'23.0"S, 151°11'41.0"E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
Nice Thread
quote:
DATEPATTERN ... Yes, that is interesting
Cool It may be 7.6, but, it's new to me...
 
Posts: 3084 | Location: Middle Tennessee | Registered: February 23, 2005Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] convert character date of mm/dd/yyy format to date format of mm/dd/yyyy

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