Solved

db2

Posted on 2009-07-01
10
1,270 Views
Last Modified: 2012-05-07
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
0
Comment
Question by:EXPEXGSK
  • 5
  • 2
10 Comments
 
LVL 45

Expert Comment

by:Kdo
ID: 24756840
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


0
 
LVL 45

Expert Comment

by:Kdo
ID: 24756877
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
0
 

Author Comment

by:EXPEXGSK
ID: 24756890
This is the row 7 column 5
6012728,"2007","NEOPLASMS",3,"2009-07-01-12.34.36.00000","System"
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 45

Expert Comment

by:Kdo
ID: 24756956

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
0
 
LVL 45

Accepted Solution

by:
Kdo earned 500 total points
ID: 24756986
Hold on a second.  :)  Looking through my reading glasses helps.  :)

The DB2 timestamp format is:  yyyy-mm-dd.mm.hh.ss.msssss

Your description has a hyphen '-' between the date and time portions, and uses 5 digits for the microsecond clock.

DB2 expects the date/time exactly as above.  Including a period between the date and time, and a 6 digit microsecond field.

Try changing your query to:

  FormatDate = Format(DateTime, "yyyy-mm-dd.hh.mm.ss.") & "000000"


Good Luck,
Kent

0
 

Author Comment

by:EXPEXGSK
ID: 24756992
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
0
 
LVL 45

Expert Comment

by:Kdo
ID: 24995460
Hi Angel,

There is a definitive answer here:

  http://www.experts-exchange.com/Database/DB2/Q_24537231.html?cid=238#a24756986


Kent
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OPENQUERY INSERT not recognizing linked server 12 562
RPG to c# 3 362
Can I insert a row into a MS SQL table from a SQLRPGLE program? 5 133
RPG Free Parital key and move replacement 3 112
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question