Hi All I am trying to create a databricks sql to read from d

Question

Hi All I am trying to create a databricks sql to read from delta table using jdbc table sql format and insert the records into Oracle database The connection is established and records have been inserted but one of the date columns has a valid date yyyy mm dd in delta table but gets inserted as Timestamp Though the oracle table is defined as DATE for the particular column databricks process somehow converts it to timestamp and loads it I did try workarounds date format to date etc it does not work out though I can convert it to STRING and load them I want to understand why date column is converted into timestamp `Serde Library org apache hadoop hive serde2 lazy LazySimpleSerDe` `InputFormat org apache hadoop mapred SequenceFileInputFormat` `OutputFormat org apache hadoop hive ql io HiveSequenceFileOutputFormat` `Partition Provider Catalog` `Schema root` ` code string nullable = true ` ` curr rec ind integer nullable = true ` ` strt dt string nullable = true ` ` end dt string nullable = true ` ` false` ` 4 SubqueryAlias` `Arguments spark catalog db edw sesn map` ` 5 Project` `Arguments code 2132 CURR REC IND 2136 to date STRT DT 2137 Some yyyy MM dd AS STRT DT 2123 to date end dt 2138 Some yyyy MM dd AS end dt 2124 ` ` 6 Project` `Arguments ansi cast code 2132 as string AS code 2140 ansi cast CURR REC IND 2136 as decimal 1 0 AS CURR REC IND 2144 ansi cast STRT DT 2123 as timestamp AS STRT DT 2145 ansi cast end dt 2124 as timestamp AS END DT 2146 ` Thanks Mohan

Paddy · Answer

What s exactly the query and connector `to date` will return a DATE in a dataframe but it s depending on the connector whether to keep it as DATE or do something magic conversions

Mohan · Answer

oracle jdbc connector and please find the sql below DROP TABLE IF EXISTS db test sesn CREATE TABLE IF NOT EXISTS db test sesn USING org apache spark sql jdbc OPTIONS driver oracle jdbc driver OracleDriver url jdbc oracle thin test com 1521 GDVNSP dbtable STAGING test sesn orcl user test password test sql INSERT OVERWRITE db test sesn SELECT SESN YR CURR REC IND TO DATE STRT DT yyyy MM dd AS STRT DT TO DATE end dt yyyy MM dd AS end dt FROM db sesn map stg

jonathan rowe · Answer

you do know oracle DATE type also stores the time

Mohan · Answer

Thanks < jonathan rowe> yes it does got it