How to covert date which is in YYYY-MM-DD 00:00:00:000 to DD/MM/YYYY

[P_Kale]

Hi,

How to convert date which is in YYYY-MM-DD 00:00:00:000 to DD/MM/YYYY. Expected out-put 27/12/2021

CHDRNUM	NEW_RECPTDT
00000314	2021-12-27 00:00:00.000

Thanks in advance

@marcus_sommer 

[steeefan]

One possibility:

Date(Date#(NEW_RECPTDT, 'YYYY-MM-DD hh:mm:ss.fff'), 'DD/MM/YYYY') AS NEW_RECPTDT_DT

[Akash_Pohare]

You can set the global variable to -  SET DateFormat='DD/MM/YYYY';

and then use Date(Floor(NEW_RECPTDT)) AS NEW_RECPTDT_Updated in the script.

[P_Kale]

Thanks @steeefan 

When i am trying at my end the column is become blank.

[qv_testing]

May be this...

LOAD *, Date(Date#(SUBFIELD(NEW_RECPTDT,'.',1),'YYYY-MM-DD h:mm:ss'),'DD/MM/YYYY') as NewField;

[steeefan]

Are you sure your timestamp is exactly formatted as 2021-12-27 00:00:00.000? This code works for me on the format and data you specified. I also added a second option:

LOAD
  CHDRNUM,
  NEW_RECPTDT,
  Date(Date#(NEW_RECPTDT, 'YYYY-MM-DD hh:mm:ss.fff'), 'DD/MM/YYYY') AS NEW_RECPTDT_DT1,
  Date(Date#(Left(NEW_RECPTDT, 10), 'YYYY-MM-DD'), 'DD/MM/YYYY') AS NEW_RECPTDT_DT2;
LOAD * INLINE [
  CHDRNUM, NEW_RECPTDT
  00000314, 2021-12-27 00:00:00.000
];

[Pablo007]

Dear @P_Kale,

You can try:

Alt(Date#(NEW_RECPTDT,'YYYY/MM/DD'),Date#(NEW_RECPTDT,'DD/MM/YYYY')) as NEW_RECPTDT2,

[P_Kale · Accepted solution]

Hi @steeefan I have checked the actual value stored in the column and it is a number so i have used below function to get solution.

date(num#(NEW_RECPTDT),'DD/MM/YYYY') AS NEW_RECPTDT

Thanks