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

chiruinfo5262 — Mon, 26 May 2025 15:04:23 GMT

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

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

nikhilj0421 — Mon, 26 May 2025 16:50:32 GMT

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

Could you please elaborate more on it?

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

nikhilj0421 — Mon, 26 May 2025 16:52:18 GMT

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

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

chiruinfo5262 — Mon, 26 May 2025 22:43:58 GMT

Hi @nikhilj0421

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

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

Granty — Wed, 03 Sep 2025 02:17:02 GMT

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.

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

eaglesky — Thu, 07 May 2026 07:45:01 GMT

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

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

nayan_wylde — Thu, 07 May 2026 15:10:19 GMT

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.

https://databrickslabs.github.io/lakebridge/

topic Trying to convert oracle sql to databricks sql but not getting the desired output in Data Engineering

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

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

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

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

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

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

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