String to Date fatal error

devesh_ssingh · Post by **devesh_ssingh** » Thu May 19, 2011 9:25 am

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

DSguru2B · Post by **DSguru2B** » Thu May 19, 2011 9:30 am

Do you want it converted into Date or timestamp?

devesh_ssingh · Post by **devesh_ssingh** » Thu May 19, 2011 9:41 am

date(10)

Etler21 · Post by **Etler21** » Thu May 19, 2011 9:52 am

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

chulett · Post by **chulett** » Thu May 19, 2011 12:03 pm

What is your target database?

devesh_ssingh · Post by **devesh_ssingh** » Thu May 19, 2011 12:11 pm

hey craig...

its Db2..

devesh_ssingh · Post by **devesh_ssingh** » Thu May 19, 2011 12:23 pm

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

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

DSguru2B · Post by **DSguru2B** » Thu May 19, 2011 12:44 pm

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.

chulett · Post by **chulett** » Thu May 19, 2011 7:13 pm

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

devesh_ssingh · Post by **devesh_ssingh** » Fri May 20, 2011 2:53 am

@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

DSguru2B · Post by **DSguru2B** » Fri May 20, 2011 7:20 am

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.

DSguru2B · Post by **DSguru2B** » Fri May 20, 2011 7:21 am

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????

chulett · Post by **chulett** » Fri May 20, 2011 7:37 am

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.

DSXchange

String to Date fatal error

String to Date fatal error

Something like this should work fine