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

Hi @MJ Jakati ,

Thanks for your update.

Please have another try with below:

select top 1 substring(CONVERT(varchar(30), deliveredtime),0,10) deliveredtime  
 from messages   
 where currentstatetype = 'Delivered' and deliveredtime <> '0'  
 and documentclass is not null and documentclass <> 'Receipt' and   
 (direction = 'Otbd' or direction = 'nal') and url like 'http:%'   
 order by deliveredtime desc  

 SELECT CONVERT(DATETIME,dateadd(second, 174532, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')  

If above are still not working, we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

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 3

Hello MelissaMa-msft ,

Thanks a lot the queries are working, Appreciate your help here.

Had one clarification can we save the query in variable in Linux script and run with sqlcmd? If yes what option we need to use?

SQL="select top 1 substring(CONVERT(varchar(30), deliveredtime),0,10) deliveredtime from messages where currentstatetype = 'Delivered' and deliveredtime <> '0' and documentclass is not null and documentclass <> 'Receipt' and (direction = 'Otbd' or direction = 'nal') and url like 'http:%' order by deliveredtime desc"

/opt/mssql-tools/bin/sqlcmd -S localhost -U XXXX -P XXXX -Q $SQL -o test.log

I am trying above command but its not working getting below error.

Sqlcmd: 'top': Unknown Option. Enter '-?' for help.

Answer 4

Don't you need quotes around $SQL?

/opt/mssql-tools/bin/sqlcmd -S localhost -U XXXX -P XXXX -Q "$SQL" -o test.log

Answer 5

Hi - ErlandSommarskog Thanks by using "$SQL" its working.

Hello MelissaMa-msft/Team,

This will be my last request sorry for bugging a lot on this.

The query which you have shared getting below output format.

SELECT CONVERT(DATETIME,dateadd(second, 174532, CAST( '1970-01-01' as datetime ) ) AT TIME ZONE 'UTC' AT TIME ZONE 'Eastern Standard Time')
2021-05-07 14:35:32.000

Can you convert into below format please.
202105071435.32

Regards,
Jakati