Case 926 Recode Date with time stamp into date format

[paulyeo11]

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 

[Vegar]

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]

[paulyeo11]

Hi Vegar

I am still getting nill

Paul

[paulyeo11]

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

[Taoufiq_Zarra]

@paulyeo11 

you can always do :

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

[Kushal_Chawda · Accepted solution]

@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]

or

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