asked on

db2

In Ms access for timestamp i give function Public Function FormatDate(DateTime As Variant) As String
FormatDate = Format(DateTime, "yyyy-mm-dd-hh.mm.ss.") & "00000"
End Function . In query i used function name query result came good but import to db2 time its giving error
SQL3129W The date, time, or timestamp field containing
""2009-07-01-12.34.36.00000"" in row "7" and column "5" was padded with
blanks.
what is the solution

Kent Olsen

Hi EXPEXGSK,

That's not really an error. SQL3129W is a warning (note that it ends with a 'W') to tell you that the field looked non-standard due to trailing blanks but DB2 ignored them. You can safely ignore the warning.

If you can post line 7, perhaps we can help find the data issue that's generating the warning.

Kent

Kent Olsen

Hi EXPEXGSK,

You're actually sending data from DB2 to ACCESS. The warning is DB2 telling you that the data being sent from DB2 is shorter than the receving field and that DB2 has padded the value with blanks.

By sending the timestamp value '2009-07-01-12.34.36.00000' in a field that is more that 26 characters, DB2 will append blanks to the right of the value and send it as a string.

The warning simply tells you that the value is padded with blanks on the right. To get rid of the warning, make sure that the receiving field is 26 characters.

Kent

EXPEXGSK

ASKER

This is the row 7 column 5
6012728,"2007","NEOPLASMS",3,"2009-07-01-12.34.36.00000","System"

Kent Olsen

The timestamp (2009-07-01-12.34.36.00000) in column 5 is exactly 25 characters. The ACCESS query apparently has requested the data and allocated a field 26 or more characters in length.

Can you post the query that is generating the error?

Kent

ASKER CERTIFIED SOLUTION

Kent Olsen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

EXPEXGSK

ASKER

Query
SELECT Int([Diagnosis_T].[Facid]) AS Expr1, Diagnosis_T.Year, nep(Now()) AS Expr4, Diagnosis_T.Neoplasms, FormatDate(Now()) AS Expr2, System1(Now()) AS Expr3
FROM Diagnosis_T;
Function
Public Function FormatDate(DateTime As Variant) As String
FormatDate = Format(DateTime, "yyyy-mm-dd-hh.mm.ss.") & "00000"
End Function

Kent Olsen

Hi Angel,

There is a definitive answer here:

https://www.experts-exchange.com/questions/24537231/db2.html?cid=238&anchorAnswerId=24756986#a24756986

Kent