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] SQL Datetime format
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] SQL Datetime format
 Login/Join
 
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, 2008Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
OK, how?


WebFOCUS 7.6.4
Windows
 
Posts: 30 | Registered: May 14, 2008Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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: 919 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
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, 2006Reply With QuoteReport This Post
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, 2007Reply With QuoteReport This Post
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, 2008Reply With QuoteReport This Post
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, 2010Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Datetime format

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