How to make a number into a date field

[Dayna]

Hello,

Probably a stupid question, I have a number field e.g. 20091220 which is supposed to be a date, this information has been outputted in this format.

Using QliKView, how do I make 20091220 into 20/12/2009. I tried to wrap Date() around it, but obviously this didn't work...

You're help would be appreciated!

Kind Regards,

Dayna

[Anonymous]

If the field is in an expression field you can set the presentation to date ...

also in the scripting the following items should be available

SET TimeFormat='h:mm:ss';
SET DateFormat='D-M-YYYY';
SET TimestampFormat='D-M-YYYY h:mm:ss[.fff]';

I assume the value if of the type 'date'? Or is it a numeric which should be interpreted as a date?

[Dayna]

Hello,

The field is a numerical value which should be interpreted as a date, but the database we use exports it a number that QlikView doesn't recognise as being a date.

Kind Regards,

Dayna

[#4 · Accepted solution]

Hi,

you can try

date(date#(FieldName, 'YYYYMMDD'), 'DD.MM.YYYY')

date# interprets your number as a date.

Kind regards.

[Anonymous]

If it's a numeric QlikView will not automatically recognize it as a date.

You should make it a date by using something like MakeDate(year,month,day)

Try splitting up the numeric to use the function ofcourse.

Hope this helps

[#6]

At work I encountered already a problem like that.

Lets assume you have a date of the type 'YYYYMMDD', eg. '20091224' and you want to show or use it like '24/12/2009'.

You need to cut certain parts of the date string and rearrange them.

I used the following way:

right

('20091224',2) & '/' & left(right('20091224',4),2) & '/' & left('20091224',4).

I hope I helped you.^^

[Dayna]

Great!! Thank you everyone for your help on this one!!!

Kind regards,
Dayna