Inserting Null Value in to Date Field - Oracle

[allavivek]

Hello,

We are using 8.7 Version, I am trying to insert null value in to a date field, in oracle,which was defined as nullable and default value as null in table.

The expression i am using is :

if Trim(lnk_tr.eff_dt) = "NULL" then SetNull() else StringtoDate(lnk_tr.eff_dt,"%yyyy-%mm-%dd")

String "NULL" is coming from input file. %yyyy-%mm-%dd is input format.

The error iam getting is :
The variable bInvalidDateTime has value 1 which is not valid in the current context.

I am sure the error is, the date value format is not valid.

But How to insert null value in to date field, which is defined as nullable in table?

Thank You

[mac4rfree85]

I feel the error is coming from StringToDate function.. Just to confirm this, can you remove the if,,, then ..else and replace it with only "StringtoDate(lnk_tr.eff_dt,"%yyyy-%mm-%dd")" and run the job.
If the error exists, then you need to look into your input columns.

[RPhani]

Hi,

IsValid() , If..Then..Else combination may work

----------------
Phani

[ssnegi]

I tested your statement with setnull() and it inserted a record into the table date field.
I got the error you are getting when I put '' instead of setnull().
So could be that some records in the file are empty instead of NULL.
Put this condition :
if Trim(lnk_tr.eff_dt) = "NULL" or Trim(lnk_tr.eff_dt) = '' then SetNull() else StringtoDate(lnk_tr.eff_dt,"%yyyy-%mm-%dd")

[ssnegi]

if IsValid('Date',StringToDate(DSLink.DT,"%yyyy-%mm-%dd"),"%yyyy-%mm-%dd") then DSLink.DT else SetNull()

[chulett]

if IsValid('Date',StringToDate(DSLink.DT,"%yyyy-%mm-%dd"),"%yyyy-%mm-%dd") then DSLink.DT else SetNull()

Sorry but that's not valid "IsValid" syntax. You use it to test a string value before attempting to convert it.

[ssnegi]

Code: Select all

if len(DSLink.DT) = 10 then if IsValid('Date',StringToDate(DSLink.DT,"%yyyy-%mm-%dd")) then StringToDate(DSLink3.DT,"%yyyy-%mm-%dd") else SetNull() else SetNull()

The earlier code gives a warning if the input string is less than length of date. So I modified the statement.
I tested this with valid (2014-12-31) and Invalid values (2014-13-32), NULL, '', ABCD,1234 and it worked correctly.

[chulett]

It's still invalid syntax and is not "working correctly", nor is there a need to check lengths. Sorry but this is pretty basic stuff, did you check the documentation for the function? Using that StringToDate function inside IsValid is the problem as it is far too early. Check validity, then convert only if it is valid:

Code: Select all

if IsValid("Date",DSLink.DT) then StringToDate(DSLink.DT,"%yyyy-%mm-%dd") else SetNull() 

The default format for date in the IsValid function is "%yyyy-%mm-%dd" so there's no need to use that format string here, use it when the date is in a non-default form. Or use it, doesn't hurt of course... just isn't needed.

[ssnegi]

Code: Select all

if IsValid("Date",DSLink.DT) then StringToDate(DSLink.DT,"%yyyy-%mm-%dd") else SetNull()

The IsValid can check Date from a string. It works correctly on all types of Valid and Invalid strings. Thanks for that Chulett...