I have column name requestdatetime with data type string. Value for requestdatetime is in format 15/Aug/2022:01:54:41 0000

I need to convert 15/Aug/2022:01:54:41 0000 into 'yyyy-MM-dd HH:mm:ss' format.

I have tried date_parse(requestdatetime,'%d/%b/%Y'':''HH:mm:ss'' '' SSS') but it not working out.

CodePudding user response：

date_parse accepts MySQL date format, try parse_datetime which accepts Java format (do not forget to add part for timezone offset - Z):

 SELECT parse_datetime('15/Aug/2022:01:54:41  0000', 'dd/MMM/yyyy:HH:mm:ss Z');

Output:

CodePudding user response：

You need to convert string to date then date to string to get expected result.

select date_format(parse_datetime('15/Aug/2022:01:54:41  0000','dd/MMM/yyyy:HH:mm:ss Z'), '%Y/%m/%d %T')

result:

2022/08/15 01:54:41