topic Re: Case 926 Recode Date with time stamp into date format in QlikView

Case 926 Recode Date with time stamp into date format

paulyeo11 — Tue, 15 Sep 2020 04:32:41 GMT

Hi All

My Raw data as below :-

SeriesName	DocDate
SME	7/2/2019 12:00:00AM
SME	7/2/2019 12:00:00AM
SME	7/2/2019 12:00:00AM
SFA	7/2/2019 12:00:00AM
SME	7/2/2019 12:00:00AM
SME	12/19/2019 12:00:00AM

My Load script :-

Directory;
LOAD Series,
DocDate,
Date(Date#([DocDate],'M/D/YYYY'), 'DD/MM/YYYY') as [date]
FROM
QUOT_SD_.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

Can some one advise me where go wrong on the above script mark in RED Color ? Now it return null.

Paul

Re: Case 926 Recode Date with time stamp into date format

Vegar — Tue, 15 Sep 2020 04:35:30 GMT

You where looking at a timestamp not a date. Try this.

Date(floor(timestamp#([DocDate],'M/D/YYYY hh:mm:ss TT') ), 'DD/MM/YYYY') as [date]

Re: Case 926 Recode Date with time stamp into date format

paulyeo11 — Tue, 15 Sep 2020 05:10:34 GMT

Hi Vegar

I am still getting nill

Paul

Re: Case 926 Recode Date with time stamp into date format

paulyeo11 — Wed, 16 Sep 2020 11:43:27 GMT

Hi All

Can some one share with me where go wrong on below script :-

Date(floor(timestamp#([DocDate],'M/D/YYYY hh:mm:ss TT') ), 'DD/MM/YYYY') as [date]

Paul

Re: Case 926 Recode Date with time stamp into date format

Taoufiq_Zarra — Wed, 16 Sep 2020 12:13:27 GMT

@paulyeo11

you can always do :

Makedate(left(subfield(DocDate,'/',3),4),subfield(DocDate,'/',1),subfield(DocDate,'/',2)) as [date]

Re: Case 926 Recode Date with time stamp into date format

Kushal_Chawda — Wed, 16 Sep 2020 14:51:38 GMT

@paulyeo11 is there is no space between hh:mm:ss (no space)AM? i.e hh:mm:ssAM ? if so try below

Date(floor(timestamp#([DocDate],'MM/DD/YYYY hh:mm:ssTT') ), 'DD/MM/YYYY') as [date]

Date(floor(timestamp#([DocDate],'MM/DD/YYYY hh:mm:ss TT') ), 'DD/MM/YYYY') as [date]