CONCAT timestamp

[njgirl]

Hi

I have a oracle date field ('YYYY-MM-DD HH24:MI:SS' ) and seq_no field (NNNN)coming from flat file and i need to concat the date and seq_no to YYYYMMDDHHMMNNN.

Here's the example
i getting date value as 2008-05-08 21:28:26.0 and seq_no as 0001. the result should be 2008050821280001.

can someone help me with the code.

[sachin1]

2008-05-08 21:28:26.0:0001 use ":" for concant

[njgirl]

Thanks Sachin. I tried doing concat just to get the date, but is returning empty value

Arg1 - 2008-05-08 21:28:26.0

OCONV(ICONV(Arg1[1,4]:Arg1[6,2]:Arg1[10,2],"MTS"),"MTH")

[sachin1]

store your incoming date value from oracle to a stage variable and use that variable like below


1.for example ICONV("2008-05-09","D-YMD[4,2,2]") = VAL

2.similary second variable to store time part. .........Iconv("02:46:40", "MTS")

3.then use OCONV function like

just for date part OCONV(VAL,"D Y[4]"):OCONV(VAL,"D M[2]"):OCONV(VAL,"D D[2]")

similarly for time part and contact with sequence value using ":"

[chulett]

Since everything is in the proper order, I wouldn't bother with the Conv brothers or stage variables, just cat the pieces together and remove the delimiters:

Code: Select all

Convert(":-","",Arg1[1,10]:Arg1[12,5]:Arg2)

Assuming the seq_no is passed in as Arg2.

[njgirl]

Thanks Sachin and Chulett for quick reply. As Chulett sugested i did concat together and it is returning the value as expected. :D

[ray.wurlod]

I would use the DIGITS Transform then concatenation.

Code: Select all

DIGITS(InLink.TheTimestamp) : InLink.seq_no

[chulett]

Close but no cigar.

[ray.wurlod]

Close enough if you believe that the date you get from Oracle should match the date picture. Where does the ".0" spring from anyway?

[chulett]

Not sure, but for some reason they wanted to drop the seconds from the timestamp as well so you can't just put the two pieces together, even without the microseconds. However, thinking about it, it can be made even simpler:

Code: Select all

Convert(":- ","",Arg1[1,16]:Arg2)

Do one substring and then add space to the list of characters to remove. Or, of course, DIGITS rather than Convert would work just as well.