Sorting of AM /PM tiem format

[Aspiring_Developer]

Hello,

Can anyone please help me with below

I have field "Tstamp" like below:-

I want to achieve sorting . 

after 11 PM ,it should takr 12/03/2020 12AM followed by 12/03/2020 01AM.

This field is coming from database directly.

Can anyone please help ?? 

[MayilVahanan · Accepted solution]

Hi @Aspiring_Developer 

Try with Dual function like below

LOAD Dual(CDate, Timestamp(Timestamp#(CDate, 'DD/MM/YYYY hh tt'), 'DD/MM/YYYY HH')) as CDate Inline
[
CDate
12/02/2020 09 PM
12/02/2020 10 PM
12/02/2020 11 PM
12/03/2020 12 AM
12/03/2020 01 AM
12/03/2020 03 AM
];

You can replace CDate with ur date field and instead of inline, u can use directly from SQL

Load *,

Dual(CDate, Timestamp(Timestamp#(CDate, 'DD/MM/YYYY hh tt'), 'DD/MM/YYYY HH')) as CDate

from ursource;