Sql query issue while connverting from oracle to SQL syntax

Question

0.00/5 (No votes)

See more:

Hi All,

I was running the following query in Oracle absolutely fine but once converted to SQL it gives the following error:

SELECT RECD_DATE, 'C' AS HED, 'SAMPLE PASSING TIME (PERCENTAGE)' AS HED_DESCR, 'AVG MIN' AS SUB_HED, DESCR, ((AVG(EM_MON_CNT)*86400)/60) AS EM_MON_CNT, 
((AVG(BP_MON_CNT)*86400)/60) AS BP_MON_CNT, ((AVG(ET_MON_CNT)*86400)/60) AS ET_MON_CNT,
((AVG(EM_YR_CNT)*86400)/60) AS EM_YR_CNT, ((AVG(BP_YR_CNT)*86400)/60) AS BP_YR_CNT, ((AVG(ET_YR_CNT)*86400)/60) AS ET_YR_CNT FROM (
select RECD_DATE, 'AVG MIN' AS SUB_HED, 'BLENDS' AS DESCR, customer_id, AVG(DATEDIFF(day,DATE_COMPLETED,RECD_DATE)) AS EM_MON_CNT, 0 AS BP_MON_CNT, 0 AS ET_MON_CNT

The error is:

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Can any one please help

What I have tried:

oracle to sql ,

Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

Posted 18-Nov-18 0:38am

Mohammad Azeem

Updated 18-Nov-18 3:32am

v2

Add a Solution

Comments

RickZeeland 18-Nov-18 7:16am

Try splitting up all those queries to see which one causes the problem.

Mohammad Azeem 18-Nov-18 7:45am

thanks Rick i sort that one out, but got a last question that is i have the following code running in oracle but not at all in SQL server.

SELECT SUBSTR(TO_CHAR(RECD_DATE,'HH24:MI:SS'),1,2)||'-'||TRIM(TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(RECD_DATE,'HH24:MI:SS'),1,2))+1),'00')) AS HED, SAMPLE_TYPE, CUSTOMER_ID, RECD_DATE,COUNT(ID_TEXT) AS CNT FROM SAMPLE
WHERE STATUS<>'X' AND SAMPLE_TYPE<>' '
GROUP BY RECD_DATE,CUSTOMER_ID,SAMPLE_TYPE ;

Giving the following error:
'TO_CHAR' is not a recognized built-in function name.

So how can i best convert this line to SQL specific from above query.

SELECT SUBSTR(TO_CHAR(RECD_DATE,'HH24:MI:SS'),1,2)||'-'||TRIM(TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(RECD_DATE,'HH24:MI:SS'),1,2))+1),'00')) AS HED,

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Answer 1 · 2018-11-18T03:32:00

I answer to your last comment I used this online converter SQLines - Online SQL Conversion[^]
on the Oracle query:

SELECT SUBSTR(TO_CHAR(RECD_DATE,'HH24:MI:SS'),1,2)||'-'||TRIM(TO_CHAR((TO_NUMBER(SUBSTR(TO_CHAR(RECD_DATE,'HH24:MI:SS'),1,2))+1),'00')) AS HED, SAMPLE_TYPE, CUSTOMER_ID, RECD_DATE,COUNT(ID_TEXT) AS CNT FROM SAMPLE 
WHERE STATUS<>'X' AND SAMPLE_TYPE<>' ' 
GROUP BY RECD_DATE,CUSTOMER_ID,SAMPLE_TYPE ;

And got this:

SELECT ISNULL(SUBSTRING(CONVERT(VARCHAR(8), RECD_DATE,108),1,2), '')+'-'+
ISNULL(RTRIM(LTRIM(TO_CHAR((TRY_CAST(SUBSTRING(CONVERT(VARCHAR(8), RECD_DATE,108),1,2) AS int)+1),'00'))), '') AS HED, 
SAMPLE_TYPE, CUSTOMER_ID, RECD_DATE,COUNT(ID_TEXT) 
AS CNT FROM SAMPLE 
WHERE STATUS<>'X' AND SAMPLE_TYPE<>' ' 
GROUP BY RECD_DATE,CUSTOMER_ID,SAMPLE_TYPE ;

Sql query issue while connverting from oracle to SQL syntax

1 solution

Solution 1

Add your solution here

Preview 0

Sql query issue while connverting from oracle to SQL syntax

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us