12 hour time(AM/PM) to 24 hour time conversion

Database
Enthusiast

12 hour time(AM/PM) to 24 hour time conversion

Hi All,

I am trying to convert 12 hour time (AM/PM) format to 24 hour time format and getting Invalid Time error.

Source data - 2:26PM

Requirement - 14:43:00

When I am running the below query it is running fine. But not giving the required 24 hour output format.

SEL CAST('02:26:00 PM' AS TIME(0) FORMAT 'HH:MI:SS');

But when I am running the below query its giving Invalid time error.

sel Cast(Substr (TRIM(START_TM),1,POSITION (':' IN START_TM) -1) || ':' ||Substr (Substr (TRIM(START_TM),(POSITION (':' IN START_TM) +1)),1,2) || ':00' || ' ' ||Substr (TRIM(START_TM),LENGTH(START_TM) -1,LENGTH(START_TM)) as time(0))

FROM TABLE1

Any help doing the conversion is appreciable.

Thanks,

Ambuj

Tags (1)
2 REPLIES
Enthusiast

Re: 12 hour time(AM/PM) to 24 hour time conversion

sel cast(cast('09/08/1989 11:46:29PM'  as timestamp(0) format 'MM-DD-YYYYBhh:mi:sst' ) as time(0))

The above query works fine but when tried the below its giving Invalid timestamp error.

sel trim(Extract(Month from Current_date))||'/'||trim(Extract(day from Current_date))||'/'||trim(Extract(year from Current_date))||' '||Substr (TRIM(START_TM),1,POSITION (':' IN START_TM) -1) || ':' ||Substr (Substr (TRIM(START_TM),(POSITION (':' IN START_TM) +1)),1,2) || ':00'||Substr (TRIM(START_TM),LENGTH(START_TM) -1,LENGTH(START_TM))

FROM GCA_APP.KRONOS_RTL_EMP_LEAVE

Thanks,

Ambuj

Enthusiast

Re: 12 hour time(AM/PM) to 24 hour time conversion

Got it.

cast(cast(LPAD(START_TM,7,'0') as timestamp(0) format 'HH:MIT') as timestamp(0) format 'HH:MI:SS') START_TM

Works fine !! Cheers :)