Timestamp with microseconds

abhilashnair · Post by **abhilashnair** » Wed Jun 25, 2014 11:17 pm

I have a comma delimited flat file as source with two columns
The first column is timestamp in the format 2013-02-27 00:00:00.000
The second column is a varchar

So data looks like
2013-02-27 00:00:00.000,abc
2013-02-28 00:00:00.000,def

and so on..

Note that the timestamp has microseconds specified with only 3 digits rather than 6

I tried using timestamp with microseconds(extended) but it is not reading properly and giving error "delimiter not seen at offset"

i copied my source file into another test file and changed the data as follows
2013-02-27 00:00:00.000000,abc
It was reading properly

So how do I go about with my original data

ssnegi · Post by **ssnegi** » Thu Jun 26, 2014 12:46 am

Read the timestamp field as varchar.
Then in transformer use conversion :
StringToTimestamp(COL,'%yyyy%mm%dd%hh%nn%ss.3')
This will convert the string to desired format of three decimal microseconds.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 26, 2014 12:53 am

Open the column metadata and specify the detailed format string for that particular timestamp field as "%yyyy-%mm-%dd %hh:%nn:%ss.3".

Set the data type as Timestamp Microsecond 23 3

These settings override the default settings.

abhilashnair · Post by **abhilashnair** » Thu Jun 26, 2014 4:57 am

Ray..thanks a lot...
Your reply saved my day

abhilashnair · Post by **abhilashnair** » Thu Jul 17, 2014 6:52 am

Reopening this discussion due to a new issue

The Sequential File Stage reads data correctly when using the timestamp format suggested by Ray but appends 3 digits to the data

For eg if the source data is 1999-12-31 12:45:36.083

the sequential files reads it as 1999-12-31 12:45:36.083000, also when I had a peek stage the same data i.e., 1999-12-31 12:45:36.083000 was in the director log. Also tried dumping the data to a target sequential file and got the same result

When I read the field as VARCHAR it looks fine

I have tried speciying length and scale as 23 and 3 respectively but to no avail

Any suggestions??

chulett · Post by **chulett** » Thu Jul 17, 2014 10:25 am

Is this actually causing a problem? Those zeroes are not significant, hence me wondering. Since you've yet to mention it, what is your actual target?

ray.wurlod · Post by **ray.wurlod** » Thu Jul 17, 2014 4:20 pm

Use the same metadata and format string for the target that you used for the source.

abhilashnair · Post by **abhilashnair** » Fri Jul 18, 2014 2:53 am

Target will be Teradata 14.10

As chulet suggests, the appending of zeroes should not be a problem(hopefully....have to confirm about it from my testing team) but I was wondering whether this is expected behaviour, because since i am explicitly mentioning the timestamp format , it should not append zeroes on its own.

Also as I mentioned before this is happening in view data and peek stage as well

chulett · Post by **chulett** » Fri Jul 18, 2014 8:09 am

Many items use 'default' formats for displaying the data, that includes View Data and Peek which can confuse people. If you follow Rays advice for the Teradata push it should use the requested format, I would think.

I was hoping for a more detailed target answer - what is the data type of the target field? Assuming we all know but just wanted to get that detail in the thread.

abhilashnair · Post by **abhilashnair** » Fri Jul 18, 2014 10:43 am

Target field is TIMESTAMP(6). I understand teradata supports either TIMESTAMP(0) Timestamp(6) with 6 digit millisecond

The target stage is a Teradata Connector Stage and I am on DataStage V 9.1

Not sure if I can specify format like Ray suggested

chulett · Post by **chulett** » Fri Jul 18, 2014 10:57 am

OK... then why all of this concern to force it to 3?