Formatting a date variable in the script?

[Anonymous]

Hi

I have an application where I have made a pivot table. The user can export the data to Excel.

The date variable is shown like this in the table: DD-MM-YYYY, but a user have reported to me, that when she export the data to her Excel, the variable is shown like this: MM-DD-YYYY.

I have looked in properties in the Numbers tab, but there I can not see my variable/dimension. Only my expressions.

Is it possible to define the dateformat in the script, where I load in the data?

The data is loaded like this in Qlikview:

ODBC CONNECT TO SASWB;

Bedoem:
SQL SELECT *
FROM path.dataset;

And when I load in the data in a testversion, it is loaded in via Excel:

LOAD var1,
     var2,
     var3,
     var4,
     date,
     var6,
     var7;
FROM
[path\dataset.xlsx]
(ooxml, embedded labels, table is dataset);

Can I write something like:

LOAD var1,

     var2,

     var3,

     var4,

     date format 'DD-MM-YYYY' ,

     var6,

     var7;

FROM

[path\dataset.xlsx]

(ooxml, embedded labels, table is dataset);

And how can I make this formatting when I read the data like this?:

Bedoem:

SQL SELECT *

FROM path.dataset;

Kind regards from Maria

[Anil_Babu_Samineni]

You may write like below

LOAD var1,

     var2,

     var3,

     var4,

     Date(date,'DD-MM-YYYY') as date, // I assume you have different date format not the string

     var6,

     var7;

FROM

[path\dataset.xlsx]

(ooxml, embedded labels, table is dataset);

SQL SELECT *

FROM path.dataset;

[#3]

Hi,

go to script (Ctrl+E) in the top of the page you can see

SET DateFormat='M/D/YYYY';

edit that as

SET DateFormat='DD/MM/YYYY';

I hope it helps

thanks,

[#4]

I have faced similar issue , but the below code solved my problem ,please try

Date(Date#([Field Name],'DD/MM/YYYY'),'DD-MMM-YYYY')  as [Field Name],

Note: [Filed Name] is the column name of your application and it s format is DD/MM/YYYY

Let me  know if it solves.

[Anonymous]

It allready says;

SET DateFormat='DD-MM-YYYY';

I guess Qlikview don't know which variables are date values even though it is a datevariable in SAS.

[#6]

Hi,

You can try  date(date#('date_string', 'YYYY-MM-DD'), 'DD\MM\YYYY') as date1

thanks