Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Converting SQL datetime field for use with DOWK.

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Converting SQL datetime field for use with DOWK.
 Login/Join
 
cbrady
Silver Member
posted
I've read everything I can find - and I can't seem to make this work. The original table is MS SQL. The master was generated with datetime off and the usage was set to YYMD. Now I want to use dowk on that field - but it fails if used directly on that field because it is not "really" a YYMD field. I've tried to convert it - but that is the part I can't seem to get right.

This is the field in the master:
FIELDNAME=TEST_DATE, ALIAS='Test Date', USAGE=YYMD, ACTUAL=DATE, FIELDTYPE=R,

This is what I have tried for converting it:
DAY2/YYMD=DATECVT(TEST_DATE, 'HYYMDs', 'YYMD');
DAY3/A3=DOWK(DAY2, 'A3');
DAY4/I2=DECODE DAY3( 'SUN' 1 'MON' 2 'TUE' 3 'WED' 4 'THU' 5 'FRI' 6 'SAT' 7);

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


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
FrankDutch
Virtuoso
posted Hide Post
Is it in the database available as HYYMDs?




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
cbrady
Silver Member
posted Hide Post
In the SQL table it is a standard SQL datetime field. I generated the master with datetime off.


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
cbrady
Silver Member
posted Hide Post
If I do this:
DAY3/A3=DOWK(TEST_DATE, 'A3');

I get this error:
(FOC36355) INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION DOWKI


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
Dan Satchell
Virtuoso
posted Hide Post
You might give functions HNAME or HPART a try. They can be used to extract day of week (dw) from datetime data. Also, function HDATE is an easier way to convert datetime fields to Smart Dates.

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
cbrady
Silver Member
posted Hide Post
Hi Dan,

Appreciate the help - but that does not work either.

If I had generated it with datetime on - the field in the master file would have been defined as 'HYYMDs' - but I generated it with datetime off. If I try to use it in any field that does require a time compnonent (HDATE,HPART, etc) it behaves as if it is a YYMD (and fails as YYMD is not a datetime format). If I try to use it with DOWK, which require a YYMD format - it behaves as if it is NOT a YYMD field.

This:
DDATE/YYMD=HDATE(TEST_DATE, 'YYMD');

Results:
(FOC36355) INVALID TYPE OF ARGUMENT #1 FOR USER FUNCTION HDATE


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
FrankDutch
Virtuoso
posted Hide Post
you can create an other line in your master

FIELDNAME=DAYTIME, ALIAS='Test Date', USAGE=HYYMDs, ACTUAL=HYYMDs, FIELDTYPE=R,


this will show you the date and time




Frank
prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Dan Satchell
Virtuoso
posted Hide Post
Sorry I didn't remember this earlier, but DOWK is a legacy function and requires legacy dates as input. Try this:

DAY2/I8YYMD = TEST_DATE ;
DAY3/A3     = DOWK(DAY2,'A3');
DAY4/I2     = DECODE DAY3( 'SUN' 1 'MON' 2 'TUE' 3 'WED' 4 'THU' 5 'FRI' 6 'SAT' 7);


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
cbrady
Silver Member
posted Hide Post
Frank & Dan - I'd like to thank you both.

Both solutions worked. I can't believe I overlooked that dowk uses legacy dates - thank you for pointing that out.


prod: WF 8.1 On Win2012
database: msSQL2012
test: identical to prod
 
Posts: 30 | Registered: February 28, 2008Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Converting SQL datetime field for use with DOWK.

Copyright © 1996-2020 Information Builders