MDEV-9343 Copying from YEAR to DATE result in '0000-00-00'

[HazemRawi]

trafficstars

When converting YEAR values to DATE/DATETIME, explicitly set month and day to zero (e.g., 2000 → 2000-00-00). Validate against SQL modes:

• Allow 2000-00-00 if NO_ZERO_IN_DATE is disabled
• Reject if NO_ZERO_IN_DATE is enabled

• [x] The Jira issue number for this PR is: MDEV-9343

Description

This fix explicitly targets YEAR to DATE or DATETIME conversions (setting month=0, day=0). Queries involving YEAR(), MONTH(), or DAY() functions will change its behavior because they rely on the same conversion logic (number_to_datetime_or_date, str_to_datetime_or_date_body) used for date parsing.

Release Notes

The server now preserves the year value when converting YEAR to DATE or DATETIME, resulting in YYYY-00-00.

How can this PR be tested?

Run ./mtr main.type_date

Basing the PR against the correct MariaDB version

• [ ] This is a new feature or a refactoring, and the PR is based against the main branch.
• [x] This is a bug fix, and the PR is based against the earliest maintained branch in which the bug can be reproduced.

PR quality check

• [x] I checked the CODING_STANDARDS.md file and my PR conforms to this where appropriate.
• [x] For any trivial modifications to the PR, I am ok with the reviewer making the changes themselves.