Date Format Change

[#1]

“How to change the date (eg. Excel source field - 2 Sep 2014) to 09/2014 format using Qlik view?”

[jyothish8807]

Hi Flavian,

Try this:

Date(Date#(YourField,'DD MMM YYYY'),'MM/YYYY') as New Date

Regards

KC

[jagan]

Hi,

Try like this

LOAD

*,

Date(Date#(DateFieldName, 'DD MMM YYYY'), 'MM/YYYY') AS NewDateField

FROM DataSource;

OR

LOAD

*,

Date(DateFieldName, 'MM/YYYY') AS NewDateField

FROM DataSource;

Note : Replace DateFieldName with your actual field name.

Hope this helps you.

Regards,

jagan.

[CELAMBARASAN · Accepted solution]

Hope QlikView automatically take it as Date format if realy formally the format is date in excel, You only need to convert it as to different format as needed.

Load *,

     Date(MonthStart(FieldName), 'MM/YYYY') AS MonthYear

From Excel;

or if it is formatted as text in excel then use

Load *,

     Date(MonthStart(Date#(FieldName, 'DD MMM YYYY')), 'MM/YYYY') AS MonthYear

From Excel;

[#5]

Sorry, I am a beginner of QV. Can you edit how date conversion should be for the following script. Want to change the 'Incident Date' to a new date.

Service,
     [Type Of Employment],
     ID,
     [Incident Date],
     Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident,

FROM


(ooxml, embedded labels, table is Sheet1);

[CELAMBARASAN]

Assuming that this is part of a load statement, since LOAD keyword is missing

LOAD

Service,
     [Type Of Employment],
     ID,
     [Incident Date],

     Date(MonthStart([Incident Date]), 'MM/YYYY')  AS [Incident MonthYear],

//or use Date(MonthStart(Date#([Incident Date], 'DD MMM YYYY')), 'MM/YYYY')  AS [Incident MonthYear],
     Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident
FROM

(ooxml, embedded labels, table is Sheet1);

[jyothish8807]

Hi Flavian,

Try this:

Table1:

load

Service,
     [Type Of Employment],
     ID,

Date(Date#([Incident Date],'DD MMM YYYY'),'MM/YYYY') as New Date,

    Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident,

FROM


(ooxml, embedded labels, table is Sheet1);

Regards

KC

[#8]

Hi

When I reload with that statement, I get a error like below;

Unknown statement

Service,

,

ID,

,

Date(MonthStart(), 'MM/YYYY') AS ,

Age,

Gender,

SAC,

,

Surname,

,

Incident

FROM

(ooxml, embedded labels, table is Sheet1)

[PrashantSangle]

Hi,

Try this,

Load

     Service,
     [Type Of Employment],
     ID,

     [Incident Date],
     Date([Incident Date],'MM/YYYY') as NewIncidentDate,

     //If above statement Wont work then try below commented statement.

     //Date(Date#([Incident Date],'DD MMM YYYY'),'MM/YYYY') as NewIncidentDate,

     Age,
     Gender,
     SAC,
     [Incident Type],
     Surname,
     [Type of Worker],
     Incident,

FROM


(ooxml, embedded labels, table is Sheet1);

reGARDS

[#10]

Hi

Date(Date#(,'DD MMM YYYY'),'MM/YYYY') as NewIncidentDate, worked.

Thanks