DSXchange

Hi,

i have searched the forum regarding this error but nothing worked for me

i have input col
Enroll_DT char(26)
UNenroll_DT char(26)

target is
Enroll_DT date(10)
UNenroll_DT date (10)

i have getting error Fatal Error: Invalid Julian day

the input value is
ENROLL_DT:2010-09-02-17.09.46.399604
UNENROLL_DT:2010-09-03-02.04.33.042327
also some values are like
UNENROLL_DT:0000-00-00


i have used folloeing derivation

StringToDate(lnk_Fnl_out.ENROLL_DT[1,10],"%yyyy-%mm-%dd")
StringToDate(lnk_Fnl_out.UNENROLL_DT[1,10],"%yyyy-%mm-%dd")

did n't worked out then
If TrimLeadingTrailing(lnk_user_dataset.UNENROLL_DT)='' Then StringToDate('0000-00-00',"%yyyy-%mm-%dd") Else StringToDate(lnk_user_dataset.UNENROLL_DT,"%yyyy-%mm-%dd")

nothing worked out

please let me know if any one can help me to sort this

Thannks
Devesh

Do you want it converted into Date or timestamp?

date(10)

StringToTimestamp((lk_read.sheet[1,4]:'-':lk_read.sheet[5,2]:'-':lk_read.sheet[7,2]) : "-" : "00" :"." : "00" :"." : "00" )

What is your target database?

hey craig...

its Db2..

@Etler21
i need to convert string to date, not timestamp..

as i said how do i tackle somthing 0000-00-00 comming from source....

Take the first 10 characters from you input data and grind it through the StringToDate() function.
For input anomalies like '0000-00-00', get the business rule and apply accordingly.

I thought (from other posts here) that DB2 wanted dates in internal format.

@DSguru,

when i apply buisness rules, used below conditions


If TrimLeadingTrailing(lnk_user_dataset.ENROLL_DT) = '0000-00-00' Or TrimLeadingTrailing(lnk_user_dataset.ENROLL_DT) = '' Then StringToDate('0001-01-01',"%yyyy-%mm-%dd") Else StringToDate(lnk_user_dataset.ENROLL_DT,"%yyyy-%mm-%dd")

but i am getting lot of warnings like

"Conversion error calling conversion routine date_from_string data may have been lost "

any suggestion to derive buisness rule so can avoid such error.

thanks

chulett wrote:I thought (from other posts here) that DB2 wanted dates in internal format.

Thats true for server. For px it has to be a date.

devesh_ssingh wrote: If TrimLeadingTrailing(lnk_user_dataset.ENROLL_DT) = '0000-00-00' Or TrimLeadingTrailing(lnk_user_dataset.ENROLL_DT) = '' Then StringToDate('0001-01-01',"%yyyy-%mm-%dd") Else StringToDate(lnk_user_dataset.ENROLL_DT,"%yyyy-%mm-%dd")

I dont see you substring'ing the first 10 characters any where in that statement above????

DSguru2B wrote:

chulett wrote:I thought (from other posts here) that DB2 wanted dates in internal format.

Thats true for server. For px it has to be a date.

Ah... thanks.