Handling DATETIME2 compatibility issue in Databricks during Hyperscale type alignment

Hi @Janice Chi
You're encountering a challenge with handling the DATETIME2 type in Azure Databricks when aligning your data to Azure SQL Hyperscale’s schema.

Since SQL Server’s DATETIME2 is not natively supported in Spark, the best practice is to use Spark's TimestampType to represent all datetime values in your Delta Lake tables. This ensures compatibility with Spark functions and avoids casting issues during transformation stages.

During the final write to Azure SQL Hyperscale (via JDBC), explicitly map the TimestampType to DATETIME2(p) (typically DATETIME2(6) for full compatibility). Most JDBC drivers (like Microsoft’s SQL Server driver) can implicitly convert Spark TimestampType into DATETIME2, as long as the SQL table is pre-defined with correct types. 

Spark's TimestampType supports up to microsecond precision (6 digits), while SQL Server’s DATETIME2(p) supports up to 100-nanosecond precision (7 digits). This means if your source timestamps exceed microsecond precision—which is uncommon—you might lose the 7th digit when writing to SQL. To ensure consistency and avoid confusion or rounding issues, it's best to align precision by using DATETIME2(6) in SQL Server when consuming timestamps from Spark. 

Best Practices for Type Casting and Validation 

• Store raw strings in Bronze, cast to TimestampType in Silver/Gold layers in Databricks. 
• Define Hyperscale schema upfront using DATETIME2(6) to avoid implicit downgrades to older SQL DATETIME. 
• Use to_timestamp() with defined formats to safely parse datetime strings. 
• Add data validation or quality checks before writing (e.g., nulls, invalid formats). 
• Monitor and log schema mismatches or precision warnings in production pipelines. 

Example Conversion flow:

from pyspark.sql.functions import col, to_timestamp

df_clean = df.withColumn("event_ts", to_timestamp(col("event_ts_str"), "yyyy-MM-dd HH:mm:ss.SSSSSS"))

# Write to Hyperscale
df_clean.write \
  .format("jdbc") \
  .option("url", jdbc_url) \
  .option("dbtable", "dbo.my_table") \
  .option("user", "username") \
  .option("password", "pwd") \
  .save()

I hope this information helps. Please do let us know if you have any further queries.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.

Thank you.