Format issue in straight table with Date?

[diane_yu]

Hello,

I'm new to a Qlikview and I'm having an issue with formatting date field. In list box, CLOSED_DATE column formats anyway I wanted but not with striahg table. Currently, it display like '42019.7131...'. When I change the format to

=date(closed_date,'mm/dd/yyyy') -> it displays like 07/15/2015.....52/15/2015...49/15/2015 and so forth...

In database, it is a datefield.

Thank you for your support in advance!

Di

[JonnyPoole]

If you are using this as a dimension, can you try this in the dimension expression:   

date(Floor([CLOSED_DATE]))

[diane_yu]

Hi Jonathan,

Yes, I'm using it as a dimension.  Your format displays in yyyy/mm/dd format.  How do we change that to mm/dd/yyyy?

Thank you.

Di

[JonnyPoole]

If the dates are accurate with the aforementioned formula, try tweak it as follows:

date(Floor([CLOSED_DATE]) , 'MM/DD/YYYY' )

[diane_yu]

Hooray!   Thank you so much for your help!

Di

[JonnyPoole · Accepted solution]

Just to close the loop...  the floor function rounds down number into integers. When you load in dates that show up as numbers with decimals, it means its a DateTime field.  Qlik can work with DateTime fields but if you want to aggregate the time values into individual whole dates ,  then the floor() function does that.  Then the date() function is used to format the number in the date format that you want.

[diane_yu]

Thank you.  I also noticed that it is case sensitive with date function in Qlikview.  I went back and changed to 'MM/DD/YYYY' and it displays correctly without using the floor function.  I understand  Qlikview is case sensitive but I did not realize it affects the Date function.

Thanks bunch!

Di

[JonnyPoole]

'mm' in qlikview means minutes so you may have been geting

minutes/day/year   !   and not month/day/year

[diane_yu]

Wow!  I never imagined 'mm' indicatesas minutes.  Thank you for the tip!

Di

[JonnyPoole]

Here are some examples i dug up in the QlikView help for reference: 

Examples (Date):

YY-MM-DD describes the date as 01-03-31.

YYYY-MM-DD describes the date as 2001-03-31.

YYYY-MMM-DD describes the date as 2001-Mar-31.

31 MMMM YYYY describes the date as 31 March 2001.

M/D/YY describes the date as 3/31/01.

W YY-MM-DD describes the date as 6 01-03-31.

WWW YY-MM-DD describes the date as Sat 01-03-31.

WWWW YY-MM-DD describes the date as Saturday 01-03-31.

Examples (Time):

hh:mm describes the time as 18:30

hh.mm.ss.ff describes the time as 18.30.00.00

hh:mm:tt describes the time as 06:30:pm

Examples (Timestamps):

YY-MM-DD hh:mm describes the timestamp as 97-03-31 18:30

M/D/Y hh.mm.ss.ffff describes the timestamp as 3/31/97 18.30.00.0000