CONCAT timestamp
Moderators: chulett, rschirm, roy
CONCAT timestamp
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.
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.
'It is the nature of things that dought shall arise'
Re: CONCAT timestamp
2008-05-08 21:28:26.0:0001 use ":" for concant
Re: CONCAT timestamp
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")
Arg1 - 2008-05-08 21:28:26.0
OCONV(ICONV(Arg1[1,4]:Arg1[6,2]:Arg1[10,2],"MTS"),"MTH")
'It is the nature of things that dought shall arise'
Re: CONCAT timestamp
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 ":"
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 ":"
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:
Assuming the seq_no is passed in as Arg2.
Code: Select all
Convert(":-","",Arg1[1,10]:Arg1[12,5]:Arg2)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I would use the DIGITS Transform then concatenation.
Code: Select all
DIGITS(InLink.TheTimestamp) : InLink.seq_no
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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:
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.
Code: Select all
Convert(":- ","",Arg1[1,16]:Arg2)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers