topic Date Format in QlikView

Date Format

agsearle — Fri, 30 Oct 2009 15:20:04 GMT

When exporting a report I am having problems with the date format.

If the extract from my table box is exported to Excel I have no problem, but I need this export in a text file. Even though in the script the date format is set to DD/MM/YYYY (and the date is correctly set in the table box, looks fine in Qlikview), I have two date fields, and in the text file one is DD/MM/YYYY and the other is YYYY-MM-DD?

The table box gets it's data from a QVW file populated from SAP.

It's driving me mad, any ideas?

Date Format

disqr_rm — Fri, 30 Oct 2009 19:28:43 GMT

Any chance you can post a sample qvw here?

Date Format

agsearle — Fri, 30 Oct 2009 20:21:01 GMT

The data is brought into QV with this script. It's a table I use for many reports.

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='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
CUSTOMCONNECT TO "xxxx;";
EVER:
LOAD ABRSPERR as Billing_Block,
 AEDAT as Date_of_Last_Change_EVER,
 ANLAGE as Installation,
 AUSZDAT as Move_Out_Date,
 EINZDAT as Move_In_Date,
 ERDAT as Date_Record_Created,
 ERNAM as Created_By,
 GEMFAKT as Joint_Invoicing,
 KOFIZ as Account_Det_ID_Contract,
 VERTRAG as Contract,
 VKONTO as CA,
 ZZ_OC as Occupancy_Nature,
 ZZWTR_SUPPLR as Other_Water_Company_ID;
SQL SELECT *
FROM EVER;
STORE EVER INTO ;
DROP TABLE EVER;

Then my report uses this data in this script:

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='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
EVER:
LOAD Installation,
 Move_Out_Date,
 Move_In_Date,
 Contract,
 CA,
 Other_Water_Company_ID
FROM  (qvd);

But when I load this into a table box, and export the data, the field 'Move_In_Date' has the date formatted as YYYY-MM-DD.

Date Format

Fri, 30 Oct 2009 21:08:20 GMT

Are you sure it is interpreting the field as a date? When you load it the first time use

date(EINZDAT) as Move_In_Date,

/Fredrik

Date Format

agsearle — Mon, 02 Nov 2009 18:19:12 GMT

Thanks Fredrik that did the trick!