Date format Changes

[Thabang231]

 Hi all

Please assist on how to convert a date field from this format 26 Jul 2022, 10:03:14 to 2022/07/26, because  when i use date function i get null values, and i cannot do date format changes on excel straight so i need to do it in the data editor please help.

Kind Regards

T

[SchalkF]

Hi

Try as below:

Test:
load * inline
[Testing
'26 Jul 2022, 10:03:14'];

Testing:
load
Date(Date#(SubField(Testing, ',', 1), 'DD MMM YYYY'), 'YYYY/MM/DD') as Date
resident Test;

drop table Test;

The subfield() will splits the string by ',' but only takes the 1st subfield, and not both, this gets the date only, then just a simple Date#() to read the date, and then converting it with Date().

Kind regards

[vinieme12]

As below 

Temp:

load

Date(Floor(timestamp#(Fieldname,'DD MMM YYYY, hh:mm:ss'))) as datefield 

[

Fieldname

26 Jul 2022, 10:03:14

];