hours to time
Moderators: chulett, rschirm, roy
hours to time
Hi
i am trying to achieve time from hours.
if my input is 170000 then my output should be 5:00 PM
to achieve this i tried using OCONV(ICONV(Arg1,"MTS),"MTS")but the result is 8:00 am
Please correct me
Thanks
Paddu
i am trying to achieve time from hours.
if my input is 170000 then my output should be 5:00 PM
to achieve this i tried using OCONV(ICONV(Arg1,"MTS),"MTS")but the result is 8:00 am
Please correct me
Thanks
Paddu
You can edit your own post, you know.
You need to get the separators in the input so it knows it is a time. I thought it was interpreting it as the number of seconds past midnight, but changing the input to 170001 got me 09:00:00 not 08:00:01 so not sure what's going on.
If you break up your field with colons or some other separator, the conversion will then work correctly. You'll need to add the 'H' option to the OConv to get AM/PM rather than military time. So something like:
A little ugly looking but should get you 05:00pm. Add the 'S' back in after the 'H' if you want the seconds as well. If you need the 'am/pm' in upper-case, you'll need to wrap an Upper() function around it. Etcetera etcetera.
You need to get the separators in the input so it knows it is a time. I thought it was interpreting it as the number of seconds past midnight, but changing the input to 170001 got me 09:00:00 not 08:00:01 so not sure what's going on.
If you break up your field with colons or some other separator, the conversion will then work correctly. You'll need to add the 'H' option to the OConv to get AM/PM rather than military time. So something like:
Code: Select all
OCONV(ICONV(Arg1[1,2]:":":Arg1[3,2]:":":Arg1[5,2],"MTS"),"MTH")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Date and time concatenate
I need to concatenate Date and Time to get Timestamp.
I am using the function
OCONV(ICONV(Arg1,"DYMD[4,2,2]"),"D-YMD[4,2,2]") : " " : OCONV(ICONV(Arg2[1,2]:":":Arg2[3,2]:":":Arg2[5,2],"MTS"),"MTHS")
Arg1=20060829
Arg2=170000
Result is 2006-08-29 05:00:00pm
But the issue is ,Orcale is not accepting this and throws an error as
ORA-01830: date format picture ends before converting entire input string
Please help me
Thanks
Paddu
I am using the function
OCONV(ICONV(Arg1,"DYMD[4,2,2]"),"D-YMD[4,2,2]") : " " : OCONV(ICONV(Arg2[1,2]:":":Arg2[3,2]:":":Arg2[5,2],"MTS"),"MTHS")
Arg1=20060829
Arg2=170000
Result is 2006-08-29 05:00:00pm
But the issue is ,Orcale is not accepting this and throws an error as
ORA-01830: date format picture ends before converting entire input string
Please help me
Thanks
Paddu
Code: Select all
OCONV(ICONV(Arg1[1,4]:"-":Arg1[5,2]:"-":Arg1[7,2],"DYMD[4,2,2]"),"D-YMD[4,2,2]") : " " : OCONV(ICONV(Arg2[1,2]:":":Arg2[3,2]:":":Arg2[5,2],"MTS"),"MTHS")
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Re: Date and time concatenate
Because, by default, Oracle is not expecting a Timestamp in that format. It wants what you had before you converted the time.paddu wrote:Result is 2006-08-29 05:00:00pm
But the issue is ,Orcale is not accepting this and throws an error as
ORA-01830: date format picture ends before converting entire input string
Code: Select all
YYYY-MM-DD HH24:MI:SS
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Here. Try this. I fixed my earlier code.
I failed to modify that the input date was in the format D-YMD and instead used the original DYMD in your post.
What Craig was pointing was, Oracle accepts Timestamp in the following format and your input (datepart) was not in that format and hence Oracle throws that error.
Code: Select all
OCONV(ICONV(Arg1[1,4]:"-":Arg1[5,2]:"-":Arg1[7,2],"D-YMD[4,2,2]"),"D-YMD[4,2,2]") : " " : OCONV(ICONV(Arg2[1,2]:":":Arg2[3,2]:":":Arg2[5,2],"MTS"),"MTHS")
What Craig was pointing was, Oracle accepts Timestamp in the following format
Code: Select all
YYYY-MM-DD HH24:MI:SS
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
Hmm... where is the corresponding change in the conversion? It looks like OConv is still delivering the time in AM/PM format and it needs it to be in Military / 24 Hour format, hence the HH24 in the picture.
So rather than the end result being '2006-08-29 05:00:00pm' it should be '2006-08-29 17:00:00'.
So rather than the end result being '2006-08-29 05:00:00pm' it should be '2006-08-29 17:00:00'.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Since you are loading into Oracle I think you would be better of using
and still be able to see the TimeStamp in the format you would like to see.
Code: Select all
Arg1[1,4]:"-":Arg1[5,2]:"-":Arg1[7,2]: " " :Arg2[1,2]:":":Arg2[3,2]:":":Arg2[5,2]
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
I tried what you gave kris but now it does not convert and nulls are not accepted in thisfield.
Individual pieces work fine
Date conversion and Time conversion but concatenate does not work .
Is there any function to convert the whole logic to convert to orcale timestamp.
Any help appreciated
Thanks
Paddu
Individual pieces work fine
Date conversion and Time conversion but concatenate does not work .
Is there any function to convert the whole logic to convert to orcale timestamp.
Any help appreciated
Thanks
Paddu
Can you post(paste) the exact derivation you are using in your job with out any manual changes? Also, your datatypes in inputstage, output OCI stage and in the Oracle table. I am out of ideas and also surprised that it is not working because I have done exactly the same in the past.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
I used this before but i realised i was doing something which makes no sense.
OCONV(ICONV(out_BURT_TOT.DATEFROM[1,4]:"-":out_BURT_TOT.DATEFROM[5,2]:"-":out_BURT_TOT.DATEFROM[7,2] : " " : out_BURT_TOT.ABIC_ZSTARTIME[1,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[3,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[5,2],"D-YMD[4,2,2]"),"D-YMD[4,2,2]")
I tried exactly what is gave and it works
out_BURT_TOT.DATEFROM[1,4]:"-":out_BURT_TOT.DATEFROM[5,2]:"-":out_BURT_TOT.DATEFROM[7,2] : " " : out_BURT_TOT.ABIC_ZSTARTIME[1,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[3,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[5,2]
DataType
out_BURT_TOT.DATEFROM Date 8
out_BURT_TOT.ABIC_ZSTARTIME Time 6
Output column Timestamp
Finally i got what i want , I took time to check with my source system where we have the Timestamp. Intermediatly we had to break these dates and times in separate fields and later when moving to target again in Timestamp format.
Thank You very much
Thanks all
Paddu
OCONV(ICONV(out_BURT_TOT.DATEFROM[1,4]:"-":out_BURT_TOT.DATEFROM[5,2]:"-":out_BURT_TOT.DATEFROM[7,2] : " " : out_BURT_TOT.ABIC_ZSTARTIME[1,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[3,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[5,2],"D-YMD[4,2,2]"),"D-YMD[4,2,2]")
I tried exactly what is gave and it works
out_BURT_TOT.DATEFROM[1,4]:"-":out_BURT_TOT.DATEFROM[5,2]:"-":out_BURT_TOT.DATEFROM[7,2] : " " : out_BURT_TOT.ABIC_ZSTARTIME[1,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[3,2]:":":out_BURT_TOT.ABIC_ZSTARTIME[5,2]
DataType
out_BURT_TOT.DATEFROM Date 8
out_BURT_TOT.ABIC_ZSTARTIME Time 6
Output column Timestamp
Finally i got what i want , I took time to check with my source system where we have the Timestamp. Intermediatly we had to break these dates and times in separate fields and later when moving to target again in Timestamp format.
Thank You very much
Thanks all
Paddu