Date format

[bharatkishore]

Hi I have dates like below format:

Now when write the following condition in script only for few dates are converting and for remaining it is not converting. Below image for reference.

code written in script date(MonthStart(Date#(Technical_Start_Date,'D/MM/YYYY')),'YYYY-MM') as Technical_Start_Date,

Can you please tell me if i am missing something.

Thanks,

Bharat

[poojashribanger]

try this

date(MonthStart(Date#(Technical_Start_Date,'DD/MM/YYYY')),'YYYY-MM')

[Sergey_Shuklin]

Hello!

You are using Date#(Technical_Start_Date,'D/MM/YYYY') for dates like 12/22/1993. Is everything right here?

[poojashribanger]

hey i think you are giving wrong format in date#

try tthis

date(MonthStart(Date#(Technical_Start_Date,'MM/DD/YYYY')),'YYYY-MM')

[poojashribanger]

You Can try this

date(MonthStart(date(Date#(Technical_Start_Date,'MM/DD/YYYY'))),'YYYY-MM')

[shraddha_g]

Try date(MonthStart(Date#(Technical_Start_Date,'M/D/YYYY')),'YYYY-MM')

[dx_anupam]

Hi Bharat,

Can you please confirm the type of  Technical_Start_Date is it date or string?

if it is date please try below formula

Date(MonthStart([Order Date],0),'YYYY-MM') as Order_month

Regrads,
Anupam

[HirisH_V7]

You can use Alt function and do like this ,

Data:
LOAD *, 
Date(Alt(Date#(Date,'DD/MM/YYYY'),Date#(Date,'MM/DD/YYYY'),Date),'YYYY-MM') as Date_T2;

LOAD * INLINE [
    Date
    1/1/1950
    22/12/1993
    1/1/1950
    5/1/1987
    6/30/1993
    8/26/1993
    12/22/1993
    7/22/1985
    2/1/1996
];

HTH,

Hirish

[Anonymous]

Please use this function  Date(Date#(fieldname, 'DD-MM-YYYY'),'YYYY-MM')as new filed

[johnca]

Is the format of your date field always Month/Day/Year or ?

Assuming it is Month/Day/Year and not Day/Month/Year (as you seem to want to use) then...

Date(MonthStart(Date#(Technical_Start_Date,'M/D/YYYY')),'YYYY-MM') as Technical_Start_Date

...should work.

If it is a mix of both you're not going to get there as how is QlikView supposed to know the difference between 7/1/2018 and 1/7/2018?

Please supply a file of ALL the Technical_Start_Date from your QVD BEFORE they are transformed by your expression. To me, there is not enough clear data to evaluate here.

HTH,

John