topic Re: Date formatting in QlikView

Date formatting

Wed, 07 May 2014 15:37:41 GMT

I am reading a date from a file. It comes in as 'May 6, 2014'. I want to convert it to the numeric format (such as 41051, et al), but cannot seem to get the syntax right. All I can get it to do is simply show it as it already is...even using the 'Date#' function.

Re: Date formatting

swuehl — Wed, 07 May 2014 15:38:46 GMT

What about

=num(Date#('May 6, 2014','MMM D, YYYY'))

Re: Date formatting

tresB — Wed, 07 May 2014 15:39:01 GMT

Date#(YourField, 'MMM DD, YYYY')

Re: Date formatting

PrashantSangle — Wed, 07 May 2014 15:39:41 GMT

Hi,

Try this,

num(Date(Date#(dateFieldm,'MMM DD, YYYY'))) as DateNum

Regards,

Re: Date formatting

its_anandrjs — Wed, 07 May 2014 15:40:37 GMT

Write like

Num(date#('May 6, 2014','MMM D, YYYY'))

Num(date#(YourDateField,'MMM D, YYYY'))

Num(Date(date#('May 6, 2014','MMM D, YYYY'),'MMM D, YYYY'))

Re: Date formatting

Wed, 07 May 2014 15:47:24 GMT

Date:

Load * Inline

[Date

'May 6, 2014'

];

Formated:

Load

num(Date#(Date,'MMM DD, YYYY')) as FormatedDate

Resident Date;

Re: Date formatting

ToniKautto — Wed, 07 May 2014 16:04:29 GMT

What you might want to keep in mind is that all values in QlikView are dual values. this means that if you successfully format a value as a Date, then the visual value will look like a Date while the underlying numerical value will be an integer.

=Date#('May 6, 2014','MMM D, YYYY')

Re: Date formatting

maxgro — Wed, 07 May 2014 16:50:50 GMT

add this expression in a text box

=num(Date#('May 6, 2014','MMM D, YYYY'))

or this

=Date#('May 6, 2014','MMM D, YYYY')+ 0

and you'll see a number

Re: Date formatting

Wed, 07 May 2014 18:24:25 GMT

When I try

Num(Date#(Date, 'MMM,DD,YYYY')))

I get null values.

Re: Date formatting

Wed, 07 May 2014 18:31:42 GMT

I fnally got it to work. In my LOAD statement, I tried this:

(Date#(Date, 'MMM DD, YYYY')) as InterpretedDate,

Thanks for the help!!

Re: Date formatting

its_anandrjs — Wed, 07 May 2014 18:47:32 GMT

Did you try my suggestion is this wrong

Num(Date#('May 6, 2014','MMM D, YYYY'))

Num(date#(YourDateField,'MMM D, YYYY')) as NewField