date routine

[kollurianu]

Hi all,


iam using following code in my routine

if (Not(IsNull(Trim(Arg1)))) Then

Ans = Oconv((Iconv(Trim(Arg1),"D/MDY[Z,Z,4]")), "D4/MDY[2,2,4]")
End Else
Ans = Oconv(Date(),'D4/MDY')

End

if my input is and empty string then iam not getting today's date.

Any help greatly appreciated.

Thank you all

[rsaliah]

Try "if (Not(IsNull(Trim(Arg1)))) and Arg1 # '' Then ....".

Regu

[kollurianu]

Thank you Regu,
Though i did not get the reason why you asking me to do like that

still i did in my code , but now iam getting compilation error.

Code: Select all

if (Not(IsNull(Trim(Arg1)))) and Arg1 #  Then

Ans = Oconv((Iconv(Trim(Arg1),"D/MDY[Z,Z,4]")), "D4/MDY[2,2,4]")
End Else
Ans  =  Oconv(Date(),'D4/MDY')

End

compilation error is

Compiling: Source = 'DSU_BP/DSU.OraDatetimestamp', Object = 'DSU_BP.O/DSU.OraDatetimestamp'

0002 if (Not(IsNull(Trim(Arg1)))) and Arg1 # Then

^
"THENEOL" unexpected, Was expecting: '=', "LT", "LE", "GT", "GE", "NE",
"EQ", "MATCH"

1 Errors detected, No Object Code Produced.


Can any one help me,

[rsaliah]

You missed out the '' (empty string).

So your code should be

Code: Select all

if (Not(IsNull(Trim(Arg1)))) and Arg1 # ''  Then 

Ans = Oconv((Iconv(Trim(Arg1),"D/MDY[Z,Z,4]")), "D4/MDY[2,2,4]") 
End Else 
Ans  =  Oconv(Date(),'D4/MDY') 

End 

You need to remember that nulls are not always the same as empty strings.

Regu.

[ray.wurlod]

Trim() won't help the test. If it's null, it's null. You can simpllfy your logic. Reversing the logic will remove the need for Not(). And you can be less specific about the format for Iconv().

Code: Select all

If IsNull(Arg1) Then
   Ans  =  Oconv(Date(),'D4/MDY')
End Else
   Ans = Oconv((Iconv(Trim(Arg1),"DMDY")), "D4/MDY[2,2,4]") 
End

[chulett]

ps. The '#' symbol means 'not equal'.

[rsaliah]

If you use Ray's simplified code don't forget to also add a check for empty string, otherwise your problem will still persist.

[kollurianu]

Thank you Regu,

I agree to you that nulls are not same empty string , i need to insert

date in a timestamp field in a oracle table ,

if my input is an empty strin or spaces when trim it it is a null value.

so in place of empty string , i need to insert system date that is what iam
doing in the else part.

does oracle all to insert null values in a timestamp field thru ora oci
stage

Can you any help me,

Thank you all.

[srinagesh]

does oracle all to insert null values in a timestamp field thru ora oci
stage

You can insert NULL value into Oracle column of datatype DATE by using @NULL in the derivation part

[rsaliah]

if my input is an empty strin or spaces when trim it it is a null value.

This is not true. What you get is an empty string which you would test for as explained. A test using ISNULL on it's own will not work.

Have you tried the modified code from earlier?

Regu.

[kollurianu]

Hi all ,

Thanks for all your answers

i acheived in the following way,

Code: Select all

if Len(Trim(Arg1)) = '0'  Then

Ans = Oconv(Date() , "D4/MDY")

End Else
Ans = Oconv((Iconv(Trim(Arg1),"D/MDY[Z,Z,4]")), "D4/MDY[2,2,4]")

End

That worked,

Thank you