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] SQL Datetime format

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQL Datetime format
 Login/Join
 
seadog
Silver Member
posted
When attempting to write a date to a SQL Datetime field I get the following error msg:
(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF) XOPEN: 22018
: Microsoft SQL Native Client: [22018] Invalid character value for cast sp
: ecification
(FOC1740) EXECUTE ERROR : ACRONYM_DICTIONARY_MASTER_TABLE
I have the SQL field defined as Datetime and the variable holding the date as an A10. Coming from Maintain what is SQL expecting?

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


WebFOCUS 7.6.4
Windows
 
Posts: 30 | Registered: May 14, 2008Report This Post
Majid Jeddi
Guru
posted Hide Post
Hi,

You can easily cast you datetime field to Charcters in the sql statment.

Regards.


WebFocus 7.6.5
AND WebLogic server as web server
sql2005 as database server
 
Posts: 273 | Location: Europe | Registered: May 31, 2007Report This Post
seadog
Silver Member
posted Hide Post
OK, how?


WebFOCUS 7.6.4
Windows
 
Posts: 30 | Registered: May 14, 2008Report This Post
PBrightwell
Master
posted Hide Post
The answer to that depends on the database. Is this MSSQL, Oracle, DB2, etc?


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
sosterle
Gold member
posted Hide Post
Hi Pat -

Did this focal point user ever get an answer to this? I'm getting the same error and I'm using MSSQL tables.

Is there a quick fix for this? Sounds like there might be. I couldn't find anything in the manuals for both FOC1400 and FOC1740 like this, but they had an answer for FOC1400 and FOC1406 and that was to edit the EDASPROF.prf file. Is that what you were thinking?

Thanks.


PROD: WebFocus 7.6.9 on WinXP
 
Posts: 59 | Registered: October 31, 2006Report This Post
jgelona
Master
posted Hide Post
You get this error when trying to update a datetime field and the format of the input value does not match the format of the field in the .mas. For example, if your input field is a YYMD format, but the .mas defines the field as a datetime format, i.e. HYYMDS, the formats don't match. You need to use the date functions to get the input format to be the same as what is in the .mas. In this case use HDTTM to convert a smart date to a datetime format. If your input field was alpha, i.e. A8YYMD, use HINPUT to convert an alpha string to a datetime format. Here an example.

This is the field you want to update
FIELD=DB_DATE,DB_DATE,HYYMDS,HYYMDS,$

Say your updates are in a HOLD file. The input field name is UPDDT and is in YYMD format. Your code might look like this:
MODIFY FILE ABC
COMPUTE UPDDT/YYMD=;
FIXFORM FROM HOLD
MATCH KEY
ON MATCH COMPUTE DB_DATE=HDTTM(UPDDT,8,'HYYMDS');
ON MATCH UPDATE DB_DATE
ON MATCH SQL COMMIT WORK
ON MATCH/NOMATCH GOTO TOP
DATA ON HOLD
END


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006Report This Post
sosterle
Gold member
posted Hide Post
Does this still work when the updates are coming from a Maintain application?


PROD: WebFocus 7.6.9 on WinXP
 
Posts: 59 | Registered: October 31, 2006Report This Post
PBrightwell
Master
posted Hide Post
It shouldn't matter whether you are using Modify or Maintain (or SQL). The important thing is that your master is correct and the date-time is formatted correctly.


Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes
 
Posts: 755 | Location: TX | Registered: September 25, 2007Report This Post
Pascal Bellerose
Gold member
posted Hide Post
Did this problem get any positive answer?

We are experiencing the same situation here after migrating from OS/400 V5R4 to OS/400 V6R1M1


The field is defined on DB2 as "NOT NULL DEFAULT(CURRENT_TIMESTAMP)".

In the master file the filed is defined as :

FIELDNAME=CRTTSP, ALIAS=CRTTSP, USAGE=HYYMDm, ACTUAL=HYYMDm, FIELDTYPE=R,
TITLE='Update timestamp', DESCRIPTION='Update timestamp', $

Thanks

This message has been edited. Last edited by: Pascal Bellerose,


WFS/FFS/DM 7.6.4 / WFS/FFS/DM 7.6.8 / WFS/FFS/DM 7.6.9 / AS400 V5R4M0 / HTML / iSM 5.5sp2
 
Posts: 60 | Location: Kingsey-Falls, Québec, Canada | Registered: May 14, 2008Report This Post
Arif
Guru
posted Hide Post
quote:
FIELDNAME=CRTTSP, ALIAS=CRTTSP, USAGE=HYYMDm, ACTUAL=HYYMDm, FIELDTYPE=R,
TITLE='Update timestamp', DESCRIPTION='Update timestamp', $

Thanks

Okay here is what you can do in your SQL statement:
CONVERT(char(8), fieldname, 112)
Also, if you are using metadata you can have a defined field in your meta data you can have datetime field usage as YYMD or A10 in expression use = datetime field name
Last opetion is you can use a defined field in your procedure but the problem with the defined field in the procedure is you cant use your field in paramter to populate the dynamic parameter values.


WebFOCUS 7.6.10
Windows
HTML
 
Posts: 294 | Registered: March 04, 2010Report 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] SQL Datetime format

Copyright © 1996-2020 Information Builders