TPT Date format error

Tools & Utilities
Enthusiast

TPT Date format error

Hi,

I am new to TPT, and am trying to get an import working in a situation where I have to explicitly set a format for a date.  For example, here is some data:

12345|MR|10JUL1949|10

Which has the schema:

 DEFINE SCHEMA test
DESCRIPTION 'this defines the structure of the source'
(
customernumber VARCHAR(12),
title_txt VARCHAR(15),
dateofbirth VARDATE(15) FORMATIN('DDMMMYYYY') FORMATOUT('YYYY-MM-DD'),
platform VARCHAR(13)
);

This succeeds (i.e. the data is loaded correctly into the target table)

However, if I pass this data:

12345||10JUL1949|10

i.e. where the column preceeding the date is empty, I get an error from the DataConnector Producer relating to parsing the date:

TPT10675 Error: DAY value supplied in data row does not fall into supported range [1,31]

This seems to indicate that TPT is not recognising the columns correctly is one is empty?

The DataConnector is defined like this:

 DEFINE OPERATOR FILE_READER
TYPE DATACONNECTOR PRODUCER
SCHEMA test
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconn_log',
VARCHAR DirectoryPath = @SourceDir,
VARCHAR FileName = @SourceFile,
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Read',
VARCHAR TextDelimiter = '|'

);

I have located the following in the DATACONNECTOR log file, not sure if it is helpful:

 Row: 1, ColCount=5

     Col  Offset  Length  Ind Contents

       1       0       5   0  '12345'

                             x'3132333435'

       2       6       0   1  ''

                             x''

       3       7       9   0  '10JUL1949'

                             x'31304A554C31393439'

       4      17       2   0  '10'

                             x'3130'

     Target buffer OUT>

  Hexadecimal formatted display from address 003E8E90 for 19 bytes.

   0000  31 32 33 34.35 7C 7C 31.30 4A 55 4C.31 39 34 39  *12345||10JUL1949* 

   0010  7C 31 30                                         *|10             * 

     WARNING!  TPT10675: pxdatetime reformatting error.

     pmTrace entry <<<<

     Scope=3

     pmTraceLevel=1

     Saving error record in error record file.

     pmWrite entry <<<<

     RecCount=1

     Buffer->Data 

7 REPLIES
Enthusiast

Re: TPT Date format error

This is the version number from the log file too:

DataConnector UTILITY LIBRARY VERSION 15.00.00.17

Teradata Employee

Re: TPT Date format error

Thank you for the information.

Yes, we recognized that we had a bug when the provided data was NULL.

The original implementation did not account for the data being NULL.

Are you sure about 15.00.00.17?

I do not think we have that many patches for 15.0.

-- SteveF
Enthusiast

Re: TPT Date format error

The client is 15.00.00.00 actually, here is more of the log information:

     ==========================================================================

     =                                                                        =

     =                     TERADATA PARALLEL TRANSPORTER                      =

     =                                                                        =

     =              DATACONNECTOR OPERATOR VERSION  15.00.00.00               =

     =                                                                        =

     =           DataConnector UTILITY LIBRARY VERSION 15.00.00.17            =

     =                                                                        =

     =    COPYRIGHT 2001-2011, Teradata Corporation.  ALL RIGHTS RESERVED.    =

     =                                                                        =

     ==========================================================================

Teradata Employee

Re: TPT Date format error

Ok, thanks for the info.

The only version number that is important here is the operator version. 15.00.00.00.

This issue was fixed in 15.00.00.01.

Please upgrade to the latest patch (efix).

-- SteveF
Enthusiast

Re: TPT Date format error

Ok, thanks, latest version fixed the issue

Fan

Re: TPT Date format error

I have Source coulmn as varchar 20 and destination column is date format 'yyyy-mm-dd'

sql query has cast(substr(r_date,1,10) as date format ''mm.dd/yyyy')

the job error on invalid date please help on the same as i have gone thorugh many blogs still didt got an answer

Teradata Employee

Re: TPT Date format error

If your destination date format is 'yyyy-mm-dd' then that is the format in which the data must be sent to Teradata. Your SQL is cast-ing as mm.dd/yyyy (unless you meant mm/dd/yyyy, but even that would still be incorrect).

-- SteveF