How to get the fraction of seconds in the time hh:mm:ss-sss

[ravindrag]

Please tell how to get the fraction of seconds part from the date format below:
YYYY-MM-DD HH:MM:SS.SSS

.SSS indicates fraction of seconds in the above formate.

Please confirm whether there is any function or any other solution to get the fraction of seconds part (SSS) in datastage.

[chulett]

Substring comes to mind. Also Field (unless that's not supported in a PX job?):

Code: Select all

Field(YourDate,".",2,1)

[ray.wurlod]

Field() is supported in parallel Transformer stages.

[sharathsj]

Please tell how to get the fraction of seconds part from the date format below:
YYYY-MM-DD HH:MM:SS.SSS

.SSS indicates fraction of seconds in the above formate.

Please confirm whether there is any function or any other solution to get the fraction of seconds part (SSS) in datastage.

Hi all. The value SSS has to be extracted in the sequencer and has to be attached to the target file while writing. So how can this be encorporated. PLease suggest a solution.

[I_Server_Whale]

Hi all. The value SSS has to be extracted in the sequencer and has to be attached to the target file while writing. So how can this be encorporated. PLease suggest a solution.

Welcome aboard !

Extracted from where???? From a incoming record, filename? Need that information.

Also, I think you meant 'incorporated' and not 'encorporated'.

Whale.

[sharathsj]

Hi..
There is a job. When the job is run, the date+Time+Fraction of a sec need to be concatenated to the name of the file and then the file has to be created. This process should be done in the sequencer. This sequencer has 2 jobs and in both the jobs files have to be created as mentioned above.

From where it has to extracted is answered, i guess, above.

[kumar_s]

You also have microseconds_from_time() function.
You need to pass it as a parameter to a job for the file creation. The job where you select the value from, write it in to a file. Using ExecuteCommand Stage, read the file using cat filename command and use the commandOutput to pass it as a parameter to the next JobActivity stage.

[I_Server_Whale]

Hi,

Declare a parameter called FileDate in your job which is writing to sequential file. Make sure you include the parameter in the output filename field of the sequential file stage, something like

Code: Select all

e:\XXXXX\YYYYYY\ZZZZZ\Name_Of_The_File#FileDate#

And then, create a job sequence with a job activity stage calling the above job. In the parameter derivation, enter:

Code: Select all

TRIM(OCONV(@DATE,'D4-YMD[4,2,2]'),"-","A"):TRIM(OCONV(@TIME,'MTS'),":","A") 

or

Code: Select all

TRIM(OCONV(@DATE,'D4-YMD[4,2,2]'):EREPLACE(OCONV(@TIME,'MTS'),':',''),"-","A") 

Whale.

[kumar_s]

Whale - Arent you more intend to work in Server edtion

[I_Server_Whale]

Oh Gosh!!! The switch failed again.....I need to be focussing....Arrrgggghh. .No ICONV, OCONV in PX.

You can tell. Can't you? Yeah, I'm more of Server guy. Just getting my hands wet with Parallel.

Thanks kumar_s.

[DSguru2B]

Yea but it should still work in a sequence job. Try it, if it doesnt, o bugger. Plus Whale's code wont give you the franction seconds part. Are you talking about current date and timestamp to be concatenated to the target file? I am still confused on that part.

[kcbland]

Sequence jobs are Server jobs, using DS BASIC. No DS BASIC date functions give fractional seconds. You're going to need to use something from Unix to do this. I did a man on date and didn't see a switch to give fractional seconds. I suggest you add ".000".

[ray.wurlod]

System(12) gives milliseconds. You can also set $OPTIONS TIME.MILLISECONDS as a compiler directive.