topic Re: Date format in QlikView

Date format

bharatkishore — Thu, 06 Sep 2018 06:15:59 GMT

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

Re: Date format

poojashribanger — Thu, 06 Sep 2018 06:17:56 GMT

try this

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

Re: Date format

Sergey_Shuklin — Thu, 06 Sep 2018 06:21:05 GMT

Hello!

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

Re: Date format

poojashribanger — Thu, 06 Sep 2018 06:25:58 GMT

hey i think you are giving wrong format in date#

try tthis

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

Re: Date format

poojashribanger — Thu, 06 Sep 2018 06:29:32 GMT

You Can try this

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

Re: Date format

shraddha_g — Thu, 06 Sep 2018 06:51:01 GMT

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

Re: Date format

dx_anupam — Thu, 06 Sep 2018 07:12:31 GMT

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

Re: Date format

HirisH_V7 — Thu, 06 Sep 2018 07:59:42 GMT

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

Re: Date format

Anonymous — Thu, 06 Sep 2018 08:51:55 GMT

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

Re: Date format

johnca — Thu, 06 Sep 2018 17:28:33 GMT

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