topic Re: Date Format - Input in QlikView

Date Format - Input

Anonymous — Tue, 13 Jan 2015 16:29:34 GMT

Trying to use this to let QV know the date format but is not working:

Date# (DateField, 'WWW DMMMYY hh:mm:ss tt')

for a field that has:

Fri 1Aug14 01:00:28 pm

Any suggestions ?

reshmakala — Tue, 13 Jan 2015 16:34:34 GMT

Can you attach the file about what you are using?

Anonymous — Tue, 13 Jan 2015 16:36:16 GMT

I'm doing this on a chart level, not in the script.

reshmakala — Tue, 13 Jan 2015 16:40:35 GMT

Ok..What is the original data in datefield that you have and what is the format you are trying to achieve form that?

Anonymous — Tue, 13 Jan 2015 16:43:02 GMT

I'd get rid of the Fri and the 2 spaces after it with a string function first, so ending up with an expression something like this.

= date ( Date# (mid('Fri 1Aug14 01:00:28 pm' , 6 ) , 'DMMMYY hh:mm:ss tt') , 'DD MMM YYYY' )

Tue, 13 Jan 2015 16:43:33 GMT

Hi,

Try Using Timestamp#(DateField, 'WWW DMMMYY hh:mm:ss tt'))
Hope it helps.

Cheers !

reshmakala — Tue, 13 Jan 2015 16:48:11 GMT

The expression that you have written gives you 01 Aug 2014. Do you need the time too?

MarcoWedel — Tue, 13 Jan 2015 17:03:09 GMT

You could also try

=Date#(Trim(Mid(DateField,4)), 'DMMMYY hh:mm:ss TT')

=Timestamp(Date#(Trim(Mid(DateField,4)), 'DMMMYY hh:mm:ss TT'))

to convert it to your default timestamp format

This expression first deletes the Weekday (which is redundant information anyhow) and interprets the result as timestamp.

I used the trim() function because I was not sure about the number of blanks between weekday and day.

hope this helps

regards

Marco