Sql query issue while converting from oracle to SQL syntax

Question

Hello Team,

Can someone please help me out converting below oracle query to SQL server. I am finding it difficult to have it converted.
Really Appreciate your help here.

1)
select substr(deliveredtime,0,10) from (select deliveredtime from messages where currentstatetype = 'Delivered' and deliveredtime <> '0' and documentclass is not null and documentclass <> 'Receipt' and $BOUND and $URL like '${PROTOCOL}:%' order by deliveredtime desc) where rownum = 1;

2)

select to_char(new_time(to_date('01/01/1970 00:00:00','MM/DD/YYYY HH24:MI:SS')+($EPOCH_TIME - 14400)/86400,'$EST','EST'),'YYYYMMDDHH24MI.SS') from dual;

Regards,
Jakati

Answer 1

Hi @MJ Jakati ,

You could have a try with format function as below:

select FORMAT(GETDATE(),'yyyyMMddHHmm.ss')  
  
SELECT FORMAT(CONVERT(DATETIME,dateadd(second, 174532, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time'),'yyyyMMddHHmm.ss')  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks a lot all for your help here, Special thanks to @MelissaMa-MSFT for helping multiple times. Now its working fine.