topic Re: Date Format in QlikView

Date Format

Thu, 11 Dec 2014 10:58:06 GMT

I have date in the excel sheet in the format 'DD-MM-YYYY hh:mm:ss'. I load this date to Qlikview and when I display it using a list box it's format is seen as 'DD.MM.YYYY hh:mm:ss'. Why does '-' change to '.' in Qlikview? How can I load incomplete date format from excel sheet? In my case I just want my Qlikview to load date in the format 'DD-MM-YYYY hh:mm'. How can I exclude 'ss' from the date during load process?

Re: Date Format

yduval75 — Thu, 11 Dec 2014 10:59:58 GMT

SET ThousandSep=' ';

SET DecimalSep=',';

SET MoneyThousandSep=' ';

SET MoneyDecimalSep=',';

SET MoneyFormat='# ##0,00 €;-# ##0,00 €';

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

SET MonthNames='janv.;févr.;mars;avr.;mai;juin;juil.;août;sept.;oct.;nov.;déc.';

SET DayNames='lun.;mar.;mer.;jeu.;ven.;sam.;dim.';

You have to change these date format variables in the script of your application.

Re: Date Format

its_anandrjs — Thu, 11 Dec 2014 11:02:33 GMT

Hi,

Check the SET variables in the load script may be that are

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

then change it to below expression

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

And you can format the field also with this expression

Load

Time(DateField,' 'DD-MM-YYYY hh:mm') as TimeFormat

From Location;

Regards

Anand

Re: Date Format

jonathandienst — Thu, 11 Dec 2014 11:03:07 GMT

1 - QV is seeing the first loaded record in that field having '.' instead of a '-' (and formats the remainder to the same format), or else that has been defined as the format for the field in Document Properties | Number or in the chart / list box.

2 - Date(Floor(Date, 1/(24 * 60))) will remove the seconds portion from the date / time.Messing with the formats changes the display, but does not remove the seconds from the underlying value.

HTH

Jonathan

Re: Date Format

morgankejerhag — Thu, 11 Dec 2014 11:03:08 GMT

Hello,

It depends on the format string at the top of your script:

SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]'; (or something similar)

You can format the date anyway you want using the date() function. For example:

date(MyDateColumn,'DD-MM-YYYY hh:mm')