Difference between two dates

imfarhan · Post by **imfarhan** » Wed Aug 22, 2012 8:27 am

I tried to use the following code but it does not like that.
the format of my fields(Admission_DTTM) is YYYY-MM-DD

ABS(ICONV(IP_Spell_Epi_HRG_Src.ADMISSION_DTTM,'D4DMY')-ICONV(IP_Spell_Epi_HRG_Src.DISCHARGE_DTTM,'D4DMY'))

Appreciate your help

ArndW · Post by **ArndW** » Wed Aug 22, 2012 8:35 am

I would use the format 'D4-YMD' for the Iconv() and Oconv() functions. If that doesn't work then please tell us how "...does not like that..."

imfarhan · Post by **imfarhan** » Wed Aug 22, 2012 8:51 am

Sorry I should have mentioned befoere my values is date+timestamp like :
2008-07-06 06:29:00.0

I think I need to conver into Date then time , in the following example I have used only one date , I assue if this work I can easily calculate difference by using minus - .

I don't know how ICONV function works?,

The following condition I have used in Stage Variable in Transformer

ICONV((TimestampToDate(IP_Spell_Epi_HRG_Src.ADMISSION_DTTM),'D4-YMD')

Many thanks
Farhan

chulett · Post by **chulett** » Wed Aug 22, 2012 9:24 am

FYI - Un-hijacked the original post.

ArndW · Post by **ArndW** » Wed Aug 22, 2012 9:28 am

DataStage server doesn't have a date or a timestamp data type. If you do an Iconv() from a date using the correct mask ('D4-YMD' in this case) the result is an integer (the number of days since 31 DEC 1967). If you do an Iconv() from the time portion of the string using the mask 'HH:MM:SS' then you will received an integer number with the number of seconds since midnight.

Do you need the time component or do you just wish to use the date portion?

imfarhan · Post by **imfarhan** » Wed Aug 22, 2012 9:48 am

Thanks for your swift response.

I think I will calculate the difference on Date only Not timestamp.
So do I have to convert into Date first as currently the type in the db2 is TIMESTAMP and the difference values of (Admission DT) and (Discharge DT) will be in the field "Length_OF_Stay" field whose type will be VARCHAR(20)

Many thanks
Farhan

ArndW · Post by **ArndW** » Wed Aug 22, 2012 9:51 am

The values in the column in DataStage are going to be strings, so a substring will be sufficient, i.e.

Code: Select all

ICONV(IP_Spell_Epi_HRG_Src.ADMISSION_DTTM[1,10],'D4-YMD')

imfarhan · Post by **imfarhan** » Wed Aug 22, 2012 10:08 am

Stage Variable svLOS: ICONV(IP_Spell_Epi_HRG_Src.ADMISSION_DTTM[1,10],'D4-YMD')
Variable 'ICONV' not defined

ArndW · Post by **ArndW** » Wed Aug 22, 2012 10:17 am

Is this a SERVER job or a PARALLEL job?

imfarhan · Post by **imfarhan** » Wed Aug 22, 2012 10:19 am

PARALLEL JOB

ArndW · Post by **ArndW** » Wed Aug 22, 2012 10:24 am

<sigh> There is no iconv in parallel jobs. This is posted in the Server section. The solution is very different in parallel jobs.

The timestamp data read from db2 into timestamp in the job is a "real" timestamp.

The derivation you want is "daysSinceFromdate(TimeStampToDate(In.Timestamp1),TimeStampToDate(In.Timestamp2))".

imfarhan · Post by **imfarhan** » Wed Aug 22, 2012 10:40 am

Oops! My apologies , I just search on the forum and found Date Difference and replied. I will be careful next time.
Sorry to confuse you and thanks for your help its works

Kind regards
Farhan

chulett · Post by **chulett** » Wed Aug 22, 2012 10:55 am

Moved to the correct forum.

This is your thread now, so if it is working to your satisfaction please mark the thread as Resolved using the button at the top of the page.