locked
Weird Query results compared to Oracle with SQL RRS feed

  • Question

  • User670644709 posted

    Hi All, 

    I have a same query which if runs on Oracle gives the succeeding results:

    While the same query with same database structure if run in SQL gives the following results:

    All i need is the SQL results must be the same as the results given by oracle which is not date but few decimal values like '0.31512121515405145154'

    Can someone please advise..

    Friday, November 23, 2018 5:28 PM

Answers

  • User753101303 posted

    Hi,

    This is the same value handled differently :

    50*60+16=3016 seconds
    3016/ (24*60*60)=0.03490741

    Now how to best solves this depends on the situation. You are trying to write an application that supports both Oracle and SQL Server with the same queries or this is a one time port from one to the other ?

    It seems this ciolumn could be exposed as a double for Oracle and maybe as a DateTime for SQL Server (likely because the lack of a real "Duration" type). If confirmed you could try to normalize this difference on the db side but the simplest option could be to move this calculation to the client side where you'll always get a TimeSpan.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 23, 2018 6:20 PM

All replies

  • User753101303 posted

    Hi,

    This is the same value handled differently :

    50*60+16=3016 seconds
    3016/ (24*60*60)=0.03490741

    Now how to best solves this depends on the situation. You are trying to write an application that supports both Oracle and SQL Server with the same queries or this is a one time port from one to the other ?

    It seems this ciolumn could be exposed as a double for Oracle and maybe as a DateTime for SQL Server (likely because the lack of a real "Duration" type). If confirmed you could try to normalize this difference on the db side but the simplest option could be to move this calculation to the client side where you'll always get a TimeSpan.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 23, 2018 6:20 PM
  • User670644709 posted

    Hi PatriceSc, 

    Its going to be one time job as we are switching from oracle to SQL permanently, so no need to handle both situations, 

    Also we would like to handle this situation on db side so can you please advise how i can change my sql query to get the same result as of oracle and which you explained very well as well. 

    Cheers!!

    Friday, November 23, 2018 7:00 PM
  • User670644709 posted

    Thanks
    PatriceSc;

    I got the solution i changed my query to the following to get the desired result,,,,,

    select id_numeric, RECD_DATE, DATE_COMPLETED, datediff(second, RECD_DATE, DATE_COMPLETED)/ (86400.00000000000) from sample

    Friday, November 23, 2018 7:47 PM