DATEFROMPARTS: Build Dates With Explicit Components

DATEFROMPARTS builds a typed date from separate year, month and day components. I use the components directly instead of assembling ambiguous date text. That makes the input order explicit without making invalid dates valid.

Choose components rather than a display convention

A date such as the second of March can be written in several regional formats. Separate numeric components remove that text-order choice from construction. The function still needs a meaningful year, month and day.

The query names those components in their argument order. Each row supplies integer values or an explicitly typed missing component. It does not change language or date-format settings.

The first expected date is 2024-02-29, a valid leap-day date. The second is 2023-02-28. Choosing adjacent years makes the calendar context visible without deliberately evaluating an invalid February date.

The output formats a separate DateText column for a stable display. The ResultType column inspects the underlying constructed value. Formatting a result as characters does not mean the function constructed character data.

WITH Parts AS
(
    SELECT CaseId,YearPart,MonthPart,DayPart
    FROM (VALUES (1,2024,2,29),(2,2023,2,28),
        (3,CAST(NULL AS int),2,28),(4,2024,CAST(NULL AS int),1))
        AS v(CaseId,YearPart,MonthPart,DayPart)
)
SELECT CaseId,YearPart,MonthPart,DayPart,
    CONVERT(char(10),DATEFROMPARTS(YearPart,MonthPart,DayPart),23) AS DateText,
    CAST(SQL_VARIANT_PROPERTY(CAST(DATEFROMPARTS(YearPart,MonthPart,DayPart)
        AS sql_variant),'BaseType') AS nvarchar(128)) AS ResultType
FROM Parts
ORDER BY CaseId;

Keep a missing component distinct from a default

The third row supplies no year. Its expected constructed date is NULL. The fourth supplies no month and also produces a missing date.

Neither row silently chooses the current year or January. Such defaults would be an application policy. I would name that policy rather than presenting it as date construction behavior.

The expected type-property output is also NULL for those missing constructed values. There is no present variant value to inspect in that expression. The function itself always returns the date type.

I keep the original component columns in the result. They explain why construction could not produce a complete date. Returning only the final NULL would hide which component was absent.

Validate the complete calendar date

A valid month number does not guarantee a valid day for that month. February and April have different boundaries from January. Leap-year rules also affect February’s final day.

The function reports an error for invalid arguments. It is not a general correction function for imported components. This example uses valid calendar combinations and missing required values only.

If an import can contain impossible dates, establish a validation path before construction. An application can reject those records with an explicit reason. Substituting the last day of the month changes the supplied date and needs separate authorization.

I don’t assume that separate range checks are sufficient. A day between one and thirty-one can still be invalid for the selected month. The year, month and day must form one permitted date together.

Keep date construction separate from other temporal rules

The constructed date contains no time-of-day requirement. Adding midnight text is a formatting choice, not another source component. Use a suitable temporal type when the application also needs a time.

A calendar date also does not identify an offset or time zone. It can label a business day without identifying a single instant. Avoid adding time-zone conclusions to a value that never supplied that information.

I’d retain typed dates for comparisons and joins after construction. Convert to a chosen text format at an actual presentation boundary. Repeatedly reconstructing dates through regional text invites another parsing decision.

This query writes no tables and changes no session settings. Its expected rows cover a leap day, an ordinary February date and two missing-component paths. Add explicit rejection tests separately when validating a real import contract.

Component construction is useful when the source already separates the date fields. It does not prove those fields were accurately collected. Preserve the source components so a valid but incorrect calendar date can still be investigated.

Keep the source parts, and the date can always be checked.

A valid date is not proof of correct data, it is a calendar value built from parts.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.