topic Re: Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY') in QlikView

Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY')

mack2018 — Fri, 02 Nov 2018 06:51:19 GMT

Hi... any ideas to convert ( 15/04/2017 - 15/05/2017)

To get 15/05/2017 as date?.....

I tried right(field,10) as Rep_Date......

Store into qvd.....

Then load Date(Rep_Date) as Rep_Date from.......and........ didnt worked....

Even

Date(Date#(Rep_Date, 'MM/DD/YYYY' ) as Reporting Date ..... didn t worked....

Any Ideas?

Re: Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY')

vitaliichupryna — Fri, 02 Nov 2018 15:35:19 GMT

Hi Marvin,

Left(Right(Date,11),10) As Date;

and then

Date(Date#(Left(Right(Date,11),10),'DD/MM/YYYY'),'DD/MM/YYYY') As Date

Thanks,

Vitalii

Re: Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY')

Gysbert_Wassenaar — Fri, 02 Nov 2018 15:36:06 GMT

Perhaps like this: Date(Date#( Trim(Replace(Subfield(Rep_Date,'-',-1),')','')), 'MM/DD/YYYY' ),'MM/DD/YYYY') as Reporting Date

Re: Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY')

Frank_Hartmann — Fri, 02 Nov 2018 15:47:55 GMT

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';


tmp1:
Load *, date(right(Field,10)) as Date;
Load * Inline [
Field
15/04/2017 - 15/05/2017
];


store tmp1 into [test.qvd] (qvd); DROP Table tmp1;


LOAD Field, 
     Date
FROM
[test.qvd]
(qvd);

Re: Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY')

mack2018 — Fri, 02 Nov 2018 15:50:05 GMT

Thank to all of you..... today I finally solved it .... don t know why it did worked with some of the ways I tried yesterday.

Re: Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY')

mack2018 — Fri, 02 Nov 2018 15:54:25 GMT

I did with

Table A:

Right(fieldname,10)

STORE... qvd

Drop tabla A:

Table B:

Load

Date(Date#...etc

AND IT DID WORKED

Re: Convert string ( MM/DD/YYYY) to Date ('MM/DD/YYY')

fallenangel6 — Mon, 05 Nov 2018 11:13:56 GMT

i did using

date(date#(trim(SubField(Field,'-',2)),'DD/MM/YYYY')) as newdate