- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Date & Time Format Issue
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 .
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Try
timestamp#(ERDAT &' '& replace(ERZET,'-',':') , 'DD-MM-YYYY hh:mm:ss)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Tried this not working