hours to time

[paddu]

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

[paddu]

sorry i missed a quote in my previous post

OCONV(ICONV(Arg1,"MTS"),"MTS")

[chulett]

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:

Code: Select all

OCONV(ICONV(Arg1[1,2]:":":Arg1[3,2]:":":Arg1[5,2],"MTS"),"MTH")

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.

[paddu]

Hey Craig


wow that works.

Thanks alot

Paddu

[paddu]

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

[kris007]

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")

[chulett]

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

Because, by default, Oracle is not expecting a Timestamp in that format. It wants what you had before you converted the time.

Code: Select all

YYYY-MM-DD HH24:MI:SS

[paddu]

Kris- i tried your logic , it threw the same error.

Craig- I did not follow you exactly . Is there any function to convert the whole function which i am using to convert to Oracle Timestamp format ?

[kris007]

Here. Try this. I fixed my earlier code.

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")

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

Code: Select all

 YYYY-MM-DD HH24:MI:SS

and your input (datepart) was not in that format and hence Oracle throws that error.

[paddu]

No change in the error message

ORA-01830: date format picture ends before converting entire input string


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")

[chulett]

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'.

[kris007]

Since you are loading into Oracle I think you would be better of using

Code: Select all

Arg1[1,4]:"-":Arg1[5,2]:"-":Arg1[7,2]: " " :Arg2[1,2]:":":Arg2[3,2]:":":Arg2[5,2]

and still be able to see the TimeStamp in the format you would like to see.

[paddu]

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

[kris007]

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.

[paddu]

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