topic Re: Convert field and Time date to Local GMT in QlikView

Convert field and Time date to Local GMT

Sat, 27 Aug 2016 08:26:12 GMT

Hi everyone,

I have data which there are include date and time field format.

I want to change the date format to our local GMT+8 . How can I do in qlikview.

I have try to use ConvertToLocalTime function but the result turns out differently.

TRAN_DATE	TRAN_TIME	DATE_TIME	DATE_TIME_NEW
07/20/16	1:16:12 AM	07/20/2016 01:12:12	8/14/4102 4:07:34 PM
07/20/16	1:16:58 AM	07/20/2016 01:12:58	9/4/4080 2:46:56 AM

LOAD FILE_NAME,

FILE_YEAR,

FILE_DATE,

NUM,

TRAN_DATE,

TRAN_TIME,

DATE(TRAN_DATE, 'MM/DD/YYYY') & ' ' & TIME(TRAN_TIME, 'HH:MM:SS') AS DATE_TIME,

ConvertToLocalTime(DATE(TRAN_DATE, 'MM/DD/YYYY')/TIME(TRAN_TIME, 'HH:MM:SS'), 'GMT+08:00') AS DATE_TIME_NEW

Re: Convert field and Time date to Local GMT

Anil_Babu_Samineni — Sat, 27 Aug 2016 11:39:19 GMT

Perhaps this

ConvertToLocal(TRAN_DATE, 'GMT+8:00') as DATE_TIME_NEW

Re: Convert field and Time date to Local GMT

MarcoWedel — Sun, 28 Aug 2016 00:19:08 GMT

Hi,

maybe helpful:

table1:
LOAD *,
    ConvertToLocalTime(DATE_TIME, 'GMT+08:00') as DATE_TIME_NEW;
LOAD *,
    Timestamp(TRAN_DATE+TRAN_TIME) as DATE_TIME;
LOAD Date#(TRAN_DATE, 'MM/DD/YY') as TRAN_DATE,
    Time#(TRAN_TIME, 'hh:mm:ss TT') as TRAN_TIME
INLINE [
    TRAN_DATE, TRAN_TIME
    07/20/16, 1:16:12 AM
    07/20/16, 1:16:58 AM
    07/21/16, 2:16:12 PM
    07/21/16, 2:16:58 PM
];

regards

Marco

Re: Convert field and Time date to Local GMT

Mon, 29 Aug 2016 04:55:43 GMT

Thanks Marco !

Its work.

Re: Convert field and Time date to Local GMT

MarcoWedel — Mon, 29 Aug 2016 10:25:19 GMT

You're welcome.

Glad it helped

Regards

Marco