Focal Point
[SOLVED] DMYY to calendar week

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

May 23, 2012, 04:36 AM
florian.fanghaenel
[SOLVED] DMYY to calendar week
My fellow WebFocus-Users,

I am seraching for a way to convert a DMYY field in order to recieve info in which calendar week (plus year) the date fell. So what i need is a field with the format CW'/'YY.
Anyone any idea? Realy apreciate it.
Further info: if helpful my DMYY-field is also available as an integer-field.

Thanks and best regards,

Florian

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


WebFOCUS 7.6
Windows, All Outputs
May 23, 2012, 12:46 PM
jimster06
You may wish to consider the function HYYWD; it returns the year and week number from a date-time value.
It is up to you to make sure that the argument you pass to it is a date-time value.


jimster06
DevStu WF 7.6.11
W7
HTML, PDF, EXL2K
May 23, 2012, 01:15 PM
rfbowley
You might have to 'tweak' this to get exactly what you want, but I would find the number of days siince 1/1, divide by 7 and concatenate that value onto the year portion of field.

 
DEFINE FILE CAR
   FIRSTDAY/DMYY WITH COUNTRY = 01012012;
   FIRST_DAY/YYMD = FIRSTDAY;
   YOUR_FIELD/DMYY WITH COUNTRY = &DMYY ;
	LAST_DATE/YYMD = YOUR_FIELD;
   DAYS/I5 = DATEDIF(FIRST_DAY, LAST_DATE, 'D');
   WEEKS/I2 = DAYS/7;
   WEEK_ALPHA/A2 = EDIT(WEEKS);
   DATE_TXT/A8 = DATECVT(LAST_DATE, 'YYMD', 'A8YYMD') ;
   FINAL_ANSWER/A8 = WEEK_ALPHA || '/' || EDIT(DATE_TXT, '9999');
END
 



Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
May 23, 2012, 05:55 PM
Waz
Not sure if this gives you the correct week number but...

TABLE FILE TRADES
PRINT DATE_OF_TRADE
COMPUTE YEAR/YY = DATE_OF_TRADE ;
COMPUTE WEEK/W = DATE_OF_TRADE ;
COMPUTE CWYY/A7 = EDIT(WEEK) | '/' | EDIT(YEAR) ;
WHERE RECORDLIMIT EQ 100
END



Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!

May 24, 2012, 03:45 AM
Wep5622
quote:
Originally posted by Waz:
Not sure if this gives you the correct week number but...

TABLE FILE TRADES
PRINT DATE_OF_TRADE
COMPUTE YEAR/YY = DATE_OF_TRADE ;
COMPUTE WEEK/W = DATE_OF_TRADE ;
COMPUTE CWYY/A7 = EDIT(WEEK) | '/' | EDIT(YEAR) ;
WHERE RECORDLIMIT EQ 100
END


You'll want to set the WEEKFIRST parameter to either Sunday (1) or Monday (2), depending on what standard your week numbering needs to follow. The default appears to be 6 (Saturday), which makes me wonder in which country the week starts at Saturday?!?


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
May 24, 2012, 03:46 AM
florian.fanghaenel
quote:
rfbowley

Thank u rfbowley. Genius.


WebFOCUS 7.6
Windows, All Outputs
May 24, 2012, 03:50 AM
Wep5622
quote:
Originally posted by rfbowley:
You might have to 'tweak' this to get exactly what you want, but I would find the number of days siince 1/1, divide by 7 and concatenate that value onto the year portion of field.

 
DEFINE FILE CAR
   FIRSTDAY/DMYY WITH COUNTRY = 01012012;
   FIRST_DAY/YYMD = FIRSTDAY;
   YOUR_FIELD/DMYY WITH COUNTRY = &DMYY ;
	LAST_DATE/YYMD = YOUR_FIELD;
   DAYS/I5 = DATEDIF(FIRST_DAY, LAST_DATE, 'D');
   WEEKS/I2 = DAYS/7;
   WEEK_ALPHA/A2 = EDIT(WEEKS);
   DATE_TXT/A8 = DATECVT(LAST_DATE, 'YYMD', 'A8YYMD') ;
   FINAL_ANSWER/A8 = WEEK_ALPHA || '/' || EDIT(DATE_TXT, '9999');
END
 


Ah, if it were that simple! In countries that follow the ISO-8601 standard for week numbering, the first of Januari can fall in week 1 or 52 or 53, depending on whether that date is before or after Wednesday and how many weeks the previous year had.


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
May 24, 2012, 05:48 AM
florian.fanghaenel
So what would you suggest?
I'm really bad at this.


WebFOCUS 7.6
Windows, All Outputs
May 24, 2012, 08:14 AM
Tom Flynn
Valid values are ISO1-ISO7
  
SET WEEKFIRST = ISO2 
TABLE FILE EMPLOYEE
PRINT LAST_NAME FIRST_NAME 
  COMPUTE NEWSAL/D12.2M = CURR_SAL + (0.5 * CURR_SAL);
  COMPUTE DATE1/HYYMDIA = DT(20050101 09:00AM);
  COMPUTE DATE2/DMYY    = HDATE(DATE1, 'DMYY');
  COMPUTE ISODATE/A10   = HYYWD(DATE1, 'A10');
END




Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
May 24, 2012, 09:34 AM
rfbowley
Well Tom, it appears that YOU can teach an old dog a new trick :-)

Thanks, I learned something new.


Robert F. Bowley Jr.
Owner
TaRa Solutions, LLC

In WebFOCUS since 2001
May 24, 2012, 10:10 AM
Tom Flynn
Hi Robert,

Welcome, BUT, I'm probably the 'old dog'! Cool


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
May 25, 2012, 09:03 AM
utgab01
Just want to make sure you installed the 64 bit version of WF and not the 32 bit version. I made that mistake on Windows and it did not work. Installed the 64 bit version of WF and everything worked just fine.


Prod: Webfocus 7.6.4
Test: Webfocus 7.6.10
Windows
Excel, PDF, HTML