topic Date Conversion with +GMT in QlikView

Date Conversion with +GMT

Mon, 16 Nov 2015 03:31:16 GMT

Im stuck trying to convert this string into a date field:

2015-02-20 10:55:14.1840296 +12:00

I can parse the line manually like this:

Date(Left([dv_rowstartdate],10),'DD/MM/YYYY') As Startdate2,

But would prefer not to.

I was trying this:

Date#([dv_rowstartdate], 'YYYY-MM-DD hh.mm.ss.fffffffff GMT') As StartDate1,

Timestamp#([dv_rowstartdate], 'YYYY-MM-DD hh.mm.ss.fffffffff GMT') as StartdateTime1,

But I know I havent got it quite right.

PrashantSangle — Mon, 16 Nov 2015 11:20:42 GMT

Hi,

try like

Timestamp(timestamp#(trim(subfield([dv_rowstartdate],'+','1')),'YYYY-MM-DD hh:mm:ss.tttttt'))

date(date#(trim(subfield([dv_rowstartdate],'+','1')),'YYYY-MM-DD hh:mm:ss.tttttt'),'YYYY-MM-DD hh:mm:ss.tttttt')

Regards

tamilarasu — Mon, 16 Nov 2015 11:37:49 GMT

Hi Shane,

Try the below one and let me know,

Date(Date#( [dv_rowstartdate] ,'YYYY-MM-DD hh:mm:ss[.fffffff] +hh:mm')) as StartDate,

Date(Date#( [dv_rowstartdate] ,'YYYY-MM-DD hh:mm:ss[.fffffff] +hh:mm')) as StartdateTime1

Note: I just used 7 f's [.fffffff] (Milliseconds) based on your time input time format.

Tue, 17 Nov 2015 20:01:03 GMT

That worked perfect thanks.

Noob question, why are the microseconds in brackets?

tamilarasu — Wed, 18 Nov 2015 04:52:15 GMT

It will work even If you remove the square brackets. See the below thread for example.

Date(Date#( [dv_rowstartdate] ,'YYYY-MM-DD hh:mm:ss.fffffff +hh:mm'))