Solved

db2

Posted on 2009-07-01
10
1,283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
10 Comments
 
LVL 45

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 45

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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 45

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 45

Accepted Solution

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 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