date and Date# function

[#1]

Hi Friends,

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

[sunny_talwar]

What is the expression you are using?

[#3]

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

[#5]

Hi Sunny,

data not loading into that field

[sunny_talwar]

Can you show how datefield look in its raw form?

[#7]

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?

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

[#9]

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