Timestamp upto milliseconds

sharma · Post by **sharma** » Tue Feb 01, 2011 11:23 am

Can timestamp be represented in milliseconds in Datastage and What is the format ?

Currently we are using this format for timestamp is: %yyyy%mm%dd%hh%nn%ss

Can i extend it further to check incooperate milliseconds also ?

Please reply ASAP.

Ravi.K · Post by **Ravi.K** » Tue Feb 01, 2011 11:34 am

You can use the following format to represent timestamp with milliseconds part.

%yyyy%mm%dd%hh%nn%ss.n

where n is the number of fractional digits with a maximum value of 6.

sharma · Post by **sharma** » Tue Feb 01, 2011 1:02 pm

I am getting the data in this format:
20101221101010437 So this with format %yyyy%mm%dd%hh%nn%ssn is not working.

If i get the data in 20101221101010.437 ( millisecond with dot ) then %yyyy%mm%dd%hh%nn%ss.n is working fine.

So i am getting string without dot (Timestamp string of 17 characters ) which is not working with the format %yyyy%mm%dd%hh%nn%ss.n.

Do i need to use some other format ?

Please respond.

DSguru2B · Post by **DSguru2B** » Tue Feb 01, 2011 1:12 pm

Have you tried

Code: Select all

 %yyyy%mm%dd%hh%nn%ss%SSSSSS

sharma · Post by **sharma** » Tue Feb 01, 2011 1:24 pm

I am getting this error with this format.

EX: At field "exctn_ts": When validating import/export function: "timestamp_format" property value (%yyyy%mm%dd%hh%nn%ss%SSSSSS) is not a valid format.

DSguru2B · Post by **DSguru2B** » Tue Feb 01, 2011 1:34 pm

Sorry. I thought you had a 6 digit millisecond. Use three 'S' instead of six at the end of the format.

sharma · Post by **sharma** » Tue Feb 01, 2011 1:45 pm

Its still giving me the same error.

EX: At field "exctn_ts": When validating import/export function: "timestamp_format" property value (%yyyy%mm%dd%hh%nn%ss%S) is not a valid format.

DSguru2B · Post by **DSguru2B** » Tue Feb 01, 2011 1:59 pm

Not a single 'S' but three 'S's.

Code: Select all

%yyyy%mm%dd%hh%nn%ss%SSS

Unfortunately I cannot test it out as I do not have access to enterprise edition.

sharma · Post by **sharma** » Tue Feb 01, 2011 2:16 pm

Still the same error.

EX: At field "exctn_ts": When validating import/export function: "timestamp_format" property value (%yyyy%mm%dd%hh%nn%ss%SSS) is not a valid format.

The only format it takes is ".n" in the end.

Ravi.K · Post by **Ravi.K** » Wed Feb 02, 2011 9:37 am

It is an work around if you really want it as timestamp in your job.

Read it as Varchar(18) and convert to timestamp by using the following derivation and adding "."

StringToTimestamp(Inputcol[1,14]:".":Right(Inputcol,3),"%yyyy%mm%dd%hh%nn%ss.3")

sharma · Post by **sharma** » Wed Feb 02, 2011 1:09 pm

But to make this conversion, i need to use transformer.

Can you please tell me how to do the same using modifier?

sharma · Post by **sharma** » Wed Feb 02, 2011 3:02 pm

Timestamp format %yyyy%mm%dd%hh%nn%ss%SSS is working fine.
Make sure the %SSS and nor %sss.

Thanks to everybody.

Regards
~Nirmal