topic Re: Date & Time Format Issue in QlikView

Date & Time Format Issue

Wed, 04 Oct 2017 10:47:29 GMT

We have requirement to calculate time duration between two dates where we have 4 fields

Namely

From SAP

ERDAT(DD-MM-YYYY)

ERZET(HH-MM-SS)

UDATE(DD-MM-YYYY)

UTIME(HH-MM-SS)

We want to concatenate ERDAT & ERZET and UDATE & UTIME to make to date time fields and arrive hours between two dates

Already we used below functions but result is not getting due to date time format not detecting by qlikview as it is coming as text from source

Date( Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss')as DTKEY, Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') as hours, Timestamp#(ERDAT &' '& ERZET,'DD-MM-YYYY hh:mm:ss[.fff]') as hour,

Appreciate your time .

Re: Date & Time Format Issue

its_anandrjs — Wed, 04 Oct 2017 10:52:02 GMT

Try

Timestamp(Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss') as DTKEY1,

Timestamp(Timestamp#(UDATE & ' ' &UTIME,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss') as DTKEY2,

Re: Date & Time Format Issue

shraddha_g — Wed, 04 Oct 2017 10:53:18 GMT

Try

timestamp#(ERDAT &' '& replace(ERZET,'-',':') , 'DD-MM-YYYY hh:mm:ss)

Re: Date & Time Format Issue

marcus_sommer — Wed, 04 Oct 2017 10:54:26 GMT

The time-format delimiter within the expressions is ':' and in your SAP example its '-'. If those fields are already real dates and times you could use something like: timestamp(ERDAT + ERZET).

- Marcus

Re: Date & Time Format Issue

its_anandrjs — Wed, 04 Oct 2017 10:59:38 GMT

For hours difference

Load

Interval(

Timestamp(Timestamp#(ERDAT & ' ' &ERZET,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss') -

Timestamp(Timestamp#(UDATE & ' ' &UTIME,'YYYY-MM-DD hh:mm:ss.fff') ,'DD-MM-YYYY hh:mm:ss')

,'hh:mm') as Diff

From Source;

Re: Date & Time Format Issue

Wed, 04 Oct 2017 11:25:25 GMT

Tried this not working