topic Re: Date Format in QlikView

Date Format

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a date field like 2016-01-27T19:04:00.251-06:00

i want to retrieve the time hh:mm:ss from this field.

Thank you

Re: Date Format

sunny_talwar — Mon, 30 May 2016 10:07:11 GMT

May be this:

=Time(Time#(Mid('2016-01-27T19:04:00.251-06:00', 12, 8), 'hh:mm:ss'), 'hh:mm:ss')

Re: Date Format

MindaugasBacius — Mon, 30 May 2016 10:11:02 GMT

You can try using one of these statement:

=Subfield(SubField('2016-01-27T19:04:00.251-06:00', 'T', 2), '.', 1)
=mid('2016-01-27T19:04:00.251-06:00', 12, 😎

Re: Date Format

Anonymous — Mon, 30 May 2016 10:11:08 GMT

Is there any another away?@sunny

Re: Date Format

Chanty4u — Mon, 30 May 2016 10:11:23 GMT

try this

T2:

LOAD * INLINE [

a, dates

aa, 2016-01-27T19:04:00.251-06:00

];

T1:

load *,

Timestamp(Timestamp#(dates,'2016-01-27T19:04:00.251-06:00'),'hh:mm:ss') as newdate

Resident T2;

Re: Date Format

Chanty4u — Mon, 30 May 2016 10:12:16 GMT

chk image

Re: Date Format

sunny_talwar — Mon, 30 May 2016 10:13:46 GMT

There might be, what is the issue with this way? If you can elaborate on the problem you are facing using this method, we might be able to provide alternate solutions

Re: Date Format

MindaugasBacius — Mon, 30 May 2016 10:15:10 GMT

=textbetween('2016-01-27T19:04:00.251-06:00', 'T', '.')

Works also.

What is the reason why the mid statement cannot be applied?

Is there any limitations regarding Data?

Re: Date Format

Anonymous — Mon, 30 May 2016 10:15:50 GMT

Its not working chanty

Re: Date Format

Anonymous — Mon, 30 May 2016 10:17:01 GMT

Date format is not fixed

Re: Date Format

sunny_talwar — Mon, 30 May 2016 10:18:52 GMT

Try using TextBetween as Mindaugas Bacius‌ showed below

=Time(Time#(TextBetween('2016-01-27T19:04:00.251-06:00', 'T', '.'), 'hh:mm:ss'), 'hh:mm:ss')

Re: Date Format

MindaugasBacius — Mon, 30 May 2016 10:20:26 GMT

Could you than share the rules according to what the Time should appear?

Or can you share all available possibilities of that date field?

Re: Date Format

krishna20 — Mon, 30 May 2016 10:37:31 GMT

Hi ,

Try This in script

Timestamp(Dual(SubField(Column1,'T',2),Time#(SubField(Column1,'T',2),'hh:mm:ss.fff-ff:ff')),'hh:mm:ss')