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 ,

Welcome to Microsoft Q&A!

What do '$BOUND',{PROTOCOL} and $EPOCH_TIME refer? Are they column names or variables? If they are variables, you could declare them as @BOUND,@ENA and @EPOCH_TIME.

Beside, you could refer below and check whether it is helpful.

select top 1 substring(deliveredtime,0,10) 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  

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

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 Melissa,

Firstly I would say thanks for below query,

$BOUND and $URL? these are variables which are used in current linux script for oracle as we are moving from oracle to SQL server on linux we need to use replace SQL query on those linux scripts.

Below are some dummy variables which are stimualtes to actual one

BOUND="test = 'bound'"
URL="consumpurl"
PROTOCOL=$8

select top 1 substring(deliveredtime,0,10) 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

I did try to run you above query by replacing the variables to actual values,I am gettig this below error. Current deliveredtime data type is numeric

Msg 8116, Level 16, State 1, Line 15
Argument data type numeric is invalid for argument 1 of substring function.

Regards,
Jakati

Answer 4

Below is the query where variable values are been replaced.

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

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 (direction = 'Otbd' or direction = 'nal') and url like 'http:%' order by deliveredtime desc) where rownum = 1;

Answer 5

Given the environment variables and all that, I think you need to rewrite not only the SQL part, but a little more than one.

Anyway, when it comes to the queries, the common recommendation for these type of questions is that you post the CREATE TABLE statement for your table(s) and INSERT statements with sample data, enough to illustrate all angles of the problem together with the expected result. To that also add a short description of the business rules explaining why you want that results. That description should be in plain English. Not PL/SQL.