Trying to convert oracle sql to databricks sql but not getting the desired output

chiruinfo5262
New Contributor II

‎05-26-2025 08:04 AM

ORACLE SQL:

 

COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END ) SELECTED_PERIOD_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END ) COMPARISON_PERIOD_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END )/5 YEARLY_BM,
CASE WHEN COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END )>0 THEN 1 ELSE 0 END NO_OF_FAILED_ASSETS_SELECTED_PERIOD,
CASE WHEN COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END )>0 AND COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END )>0 THEN 1 ELSE 0 END NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD
, 'NONLINEAR' as classType,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE_3months AND SELECTED_PERIOD_END_DATE THEN 1 END ) Last_3_months_BM,
COUNT( CASE WHEN TRUNC(WORKORDER.REPORTDATE) BETWEEN SELECTED_PERIOD_START_DATE_6months AND SELECTED_PERIOD_END_DATE THEN 1 END ) Last_6_months_BM
FROM
(SELECT
ASSETLIST.ASSETNUM,
MONTHS.M ORDER_ID,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-12)+1) SELECTED_PERIOD_START_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M)) SELECTED_PERIOD_END_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-72)+1) COMPARISON_PERIOD_START_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-12)) COMPARISON_PERIOD_END_DATE,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-3)+1) SELECTED_PERIOD_START_DATE_3months,
TRUNC(ADD_MONTHS(LAST_DAY(SYSDATE),-MONTHS.M-6)+1) SELECTED_PERIOD_START_DATE_6months

 

 

Databricks SQL

 

COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) AS SELECTED_PERIOD_BM,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) AS COMPARISON_PERIOD_BM,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) / 5 AS YEARLY_BM,
    CASE 
        WHEN COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) > 0 
        THEN 1 ELSE 0 
    END AS NO_OF_FAILED_ASSETS_SELECTED_PERIOD,
    CASE 
        WHEN COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE AND SELECTED_PERIOD_END_DATE THEN 1 END) > 0 
        AND COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN COMPARISON_PERIOD_START_DATE AND COMPARISON_PERIOD_END_DATE THEN 1 END) > 0 
        THEN 1 ELSE 0 
    END AS NO_OF_REPEATEDLY_FAILED_ASSETS_SELECTED_PERIOD,
    'NONLINEAR' AS classType,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE_3months AND SELECTED_PERIOD_END_DATE THEN 1 END) AS Last_3_months_BM,
    COUNT(CASE WHEN DATE_FORMAT(CAST(WORKORDER.REPORTEDDATETIME AS DATE), 'yyyy-MM-dd') BETWEEN SELECTED_PERIOD_START_DATE_6months AND SELECTED_PERIOD_END_DATE THEN 1 END) AS Last_6_months_BM
 
 
 
 
 
    FROM
      (
        select ASSETLIST.asset,
    MONTHS.M AS ORDER_ID,
    
DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')), -MONTHS.M - 12),1) AS SELECTED_PERIOD_START_DATE,
  ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')), -MONTHS.M) AS SELECTED_PERIOD_END_DATE,
  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 72),1) AS COMPARISON_PERIOD_START_DATE,
  ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 12) AS COMPARISON_PERIOD_END_DATE,
  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE),'yyyy-MM-dd')), -MONTHS.M - 3),1)  AS SELECTED_PERIOD_START_DATE_3months,
  DATE_ADD(ADD_MONTHS(LAST_DAY(DATE_FORMAT(CAST(CURRENT_DATE AS DATE), 'yyyy-MM-dd')),-MONTHS.M - 6),1)  AS SELECTED_PERIOD_START_DATE_6months

 

 

 

 

 

 

Labels:
0 Kudos

nikhilj0421
Databricks Employee
Databricks Employee

‎05-26-2025 09:50 AM

Hi @chiruinfo5262 , what's the difference you are observing in the output? 

Could you please elaborate more on it?

0 Kudos

chiruinfo5262
New Contributor II
In response to nikhilj0421

‎05-26-2025 03:43 PM

Hi @nikhilj0421 

There is a difference of 18000 records in the output between oracle code output and databricks code output, not sure why

0 Kudos

nikhilj0421
Databricks Employee
Databricks Employee

‎05-26-2025 09:52 AM

Please review this for the reference: https://www.databricks.com/blog/how-migrate-your-oracle-plsql-code-databricks-lakehouse-platform

0 Kudos

Granty
New Contributor II

‎09-02-2025 07:17 PM

This is a helpful comparison! I've definitely run into similar date formatting issues when migrating queries. The Oracle TRUNC function and Databricks' DATE_FORMAT/CAST combo can be tricky to reconcile. Speaking of needing a break after debugging SQL, anyone ever unwind with a quick game? I find Snow Rider 3D oddly relaxing after staring at code all day.

0 Kudos

eaglesky
New Contributor II

‎05-07-2026 12:45 AM

Hi @Block Blast, I can’t spot the variation in the result here. Could you explain what you’re noticing in more detail?

0 Kudos

nayan_wylde
Esteemed Contributor II

‎05-07-2026 08:10 AM

You’re using date_format(...) which turns dates into strings, so BETWEEN becomes a string comparison.
 
You can also look up for databricks lakebridge that can assist you in code conversion or migrations. 
0 Kudos