Convert MSSS Fixed Number to ANSI Time "hh:mm:ss AM/PM"

Question

I am trying to convert a fixed number to time without the date as hh:mm:ss AM/PM - so 67683000 would be 06:48:03 PM; 22814000 = 06:20:14 AM. I had % 43200000 to calculate as the remainder. My MSSS SQL code (cursor) is below. In Snowflake, my code (not as a cursor) needs to be translated to ANSI but I'm getting errors and can't figure out why.

Expected results in HH:MM:SS AM (or PM)

---

MSSS - correct

`CONVERT(VARCHAR, DATEADD(ms, AS_AIRED_START_TIME % 43200000, 0), 108)  
+ '' ''  
+ CASE  WHEN AS_AIRED_START_TIME % 86400000 > 43199999       
THEN ''PM''  
ELSE ''AM'' END 

[Aired Time] `

---

ANSI - wrong

`TO_VARCHAR(255), (DATE_PART(MILLISECOND, AS_AIRED_START_TIME % 43200000, 0), 108)  
+ ' '  
+ CASE  WHEN AS_AIRED_START_TIME % 86400000 > 43199999   
THEN 'PM'  
ELSE 'AM' END     

as AiredTime`

---

ANSI - close but not exact

`TO_CHAR(TRUNC(fed.AS_AIRED_START_TIME/3600),'FM9999999999900')      
|| ':'   
|| TO_CHAR(TRUNC(MOD(fed.AS_AIRED_START_TIME,3600)/60),'FM00')    
|| ':'   
|| TO_CHAR(MOD(fed.AS_AIRED_START_TIME,60),'FM00')` 

Result: 17248:36:40

ANSI - wrong

`TO_TIMESTAMP_NTZ(fed.AS_AIRED_START_TIME,0)` 

---

ANSI - wrong

`TO_VARCHAR(DATEADD(millisecond,500, current_timestamp),'hh:mi:ss')`

---

Excel - conversion

enter image description here

so given 1800000, I would calculate as 1800000/1000/60sec/60min/24hours = 00:30:00 = 30 minutes

Answer 1

with TEST_DATA
  as (
    select 67683000::integer as AS_AIRED_START_TIME -- PM
    union all
    select 24483000::integer -- AM
)
select to_char(timeadd(ms, AS_AIRED_START_TIME, to_time('00:00:00')), 'HH12:MI:SS AM')
from TEST_DATA;