[SPARK-40014][SQL] Support cast of decimals to ANSI intervals

[amahussein]

Context

• apache/spark@3f84c7d56a
• Similar to [SPARK-40008][SQL] Support casting of integrals to ANSI intervals #6326

What changes were proposed in SPARK pull request?

support casts of decimals to ANSI intervals, and follow the SQL standard

Before:

spark-sql> SELECT CAST(1.001002BD AS INTERVAL SECOND);
Error in query: cannot resolve 'CAST(1.001002BD AS INTERVAL SECOND)' due to data type mismatch: cannot cast decimal(7,6) to interval second; line 1 pos 7;

After:

spark-sql> SELECT CAST(1.001002BD AS INTERVAL SECOND);
0 00:00:01.001002000

Why changes were needed?

To improve user experience with Spark SQL, and to conform to the SQL standard.

Does this PR introduce any user-facing change?

Yes, improve error message

Why it might affect RAPIDS?

extend the expected behavior of Ansi Casts

Impact on Testing?

Yes

Requires Doc update?

No