Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

date and Date# function

Hi Friends,

I am getting error while working on date(date#,format) function , any body could give me the help on this please.

14 Replies
sunny_talwar

What is the expression you are using?

Not applicable
Author

I have taken date field as expression.

I am using it at script level like

date(date#(datefield,'M/D/YY'),'YYYY/MM/DD')

sunny_talwar

What is the error that you get when you use this? I don't see anything wrong with the syntax

Not applicable
Author

Hi Sunny,

data not loading into that field

sunny_talwar

Can you show how datefield look in its raw form?

Not applicable
Author

Hi Sunny,

This  is my data

LOAD DEPARTMENT_ID,

     EMPLOYEE_ID,

     END_DATE,

     JOB_ID,

     date(date#(START_DATE,'yyyy/dd/mm'),'yy/d/m/') as daa

FROM

E:\ALL_HR_8_TABLES_DETAILS_VIEW.xls

(biff, embedded labels, table is [JOB_HISTORY$]);

sunny_talwar

Two things...

1) why do you have an extra / at the end here? Typo?

Capture.PNG

2) Month is MM (upper case m) and Minute is mm (lower case m)

Try this

LOAD DEPARTMENT_ID,

     EMPLOYEE_ID,

     END_DATE,

     JOB_ID,

     date(date#(START_DATE,'YYYY/DD/MM'),'YY/D/M') as daa

FROM

E:\ALL_HR_8_TABLES_DETAILS_VIEW.xls

(biff, embedded labels, table is [JOB_HISTORY$]);

Assuming START_DATE is like this in your Excel file -> 2017/05/01

Not applicable
Author

Hi sunny,

Thanks for quick response.

My date format is like 'M/D/YYYY'

sunny_talwar

And you need it to be in YY/D/M format?

LOAD DEPARTMENT_ID,

    EMPLOYEE_ID,

    END_DATE,

    JOB_ID,

    date(date#(START_DATE,'M/D/YYYY'),'YY/D/M') as daa

FROM

E:\ALL_HR_8_TABLES_DETAILS_VIEW.xls

(biff, embedded labels, table is [JOB_HISTORY$]);

M/D/YYYY - your current format

YY/D/M - your needed format

Read more here:

Why don’t my dates work?

QlikView Date fields

Get the Dates Right