Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

db2

Posted on 2009-07-01
10
Medium Priority
?
1,311 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
7 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
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 46

Expert Comment

by:Kent Olsen
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 46

Expert Comment

by:Kent Olsen
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 46

Accepted Solution

by:
Kent Olsen earned 2000 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 46

Expert Comment

by:Kent Olsen
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses
Course of the Month7 days, 8 hours left to enroll

824 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