Convert String to Timestamp

[imfarhan]

Dear All,

The data which coming from source is varchar2 = '2010-01-25' yyyy-mm-dd
I like to convert into Timestamp I've tried following function but seems not working

Code: Select all

 svApptDate =

if IsValid("date",SCH_N_Referral_Delta.FIRST_APPT_DATETIME,"%dd-%mm-%yyyy") then StringToDate(SCH_N_Referral_Delta.FIRST_APPT_DATETIME,"%dd-%mm-%yyyy") else ' '

Code: Select all

svApptDateFormat =
   TimestampFromDateTime(svApptDate,'00:00:00')

I've also tried passing the format on svAppDate to

Code: Select all

if IsValid("date",SCH_N_Referral_Delta.FIRST_APPT_DATETIME,"%yyyy-%mm-%dd") then StringToDate(SCH_N_Referral_Delta.FIRST_APPT_DATETIME,"%dd-%mm-%yyyy") else ' '

The error i'm getting is

Invalid value for this date type: **********.

Appreciate your help

[jwiles]

Try using the correct format string in your IsValid() and StringToDate() calls. You show the data format to be yyyy-mm-dd but you specify dd-mm-yyyy in the functions

The data which coming from source is varchar2 = '2010-01-25' yyyy-mm-dd

Code: Select all

if IsValid("date",SCH_N_Referral_Delta.FIRST_APPT_DATETIME,"%dd-%mm-%yyyy") then StringToDate(SCH_N_Referral_Delta.FIRST_APPT_DATETIME,"%dd-%mm-%yyyy") else ' '

Also, assuming svApptDate is a Date datatype, why not give it a default date value in the else logic? ' ' is not a valid date, ultimately giving you the error you're receiving.

Regards,

[imfarhan]

Thanks jwiles I changed it and it works

Similarly , I would like to change varchar into integer because need Lookup againt the key which is integer

Code: Select all

So the source fields (Pat_key = varchar2(10)) = Ref_table (Patk_key = integer)

I was tring to use STringToDecimal() but I don't want to keep decimal values I though there should be function for StringToIntger() but couldn't find

Appreciate help
Regards
Farhan

[ArndW]

The conversion for VarChar() to integer is implicit and doesn't need to be explicitly programmed through a function. If the VarChar() contains values that aren't convertable to integer then you should use an "IsValue('int32',Input.Column)" to ensure that no values are dropped or warnings are issued.