All Forums Database
a2kz 16 posts Joined 05/13
29 Oct 2015
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

a2kz 16 posts Joined 05/13
30 Oct 2015

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

 

a2kz 16 posts Joined 05/13
30 Oct 2015

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 :)

You must sign in to leave a comment.