CONVERT Date Styles: Use Explicit Numeric Formats

CONVERT date styles make the intended numeric display format explicit when a date becomes text. I retain the typed date until the display boundary. A formatted string does not become a new calendar value.

Start with dates rather than ambiguous date text

The query constructs two valid dates from numeric components. The first is leap day in February. The second is the fifth day of November in the same year.

DATEFROMPARTS provides the typed input without relying on an ambiguous string parser. The sample therefore isolates output formatting. It does not mix date interpretation with display style.

Style twenty-three returns year, month and day with hyphens. The expected displays are 2024-02-29 and 2024-11-05. The explicit char(10) target fits that complete format.

Style one hundred twelve returns the same components without separators. Its expected displays are 20240229 and 20241105. The explicit char(8) target fits eight digits.

WITH Inputs AS
(
    SELECT CaseId,DateValue FROM (VALUES
        (1,DATEFROMPARTS(2024,2,29)),(2,DATEFROMPARTS(2024,11,5)),
        (3,CAST(NULL AS date))) AS v(CaseId,DateValue)
)
SELECT CaseId,CONVERT(char(10),DateValue,23) AS YearMonthDay,
    CONVERT(char(8),DateValue,112) AS CompactYearMonthDay,
    CONVERT(char(10),DateValue,101) AS MonthDayYear,
    CONVERT(char(10),DateValue,103) AS DayMonthYear,
    CASE WHEN DateValue IS NULL THEN NULL
         WHEN CONVERT(date,CONVERT(char(8),DateValue,112),112)=DateValue THEN 1 ELSE 0 END AS CompactRoundTrip
FROM Inputs ORDER BY CaseId;

Different component orders represent the same date

Style one hundred one places month before day. The first expected display is 02/29/2024. The second is 11/05/2024.

Style one hundred three places day before month. Its expected displays are 29/02/2024 and 05/11/2024. The typed input date has not changed between these expressions.

The November row shows why an unlabeled slash-separated string can be ambiguous. Both its day and month numbers are valid months. A receiver needs the agreed format to interpret that text correctly.

I use four-digit years in every output. A two-digit year introduces a separate century interpretation policy. There is no reason to add that uncertainty to this small example.

Keep a complete width and an explicit round trip

The target lengths are specified rather than left to an implicit default. A too-short text target can lose required characters. A style number alone does not guarantee that the receiving field is wide enough.

The final column converts the compact text back to date with the same explicit style. Both present rows expect a successful equality result of one. This checks the selected format within this example.

That round trip does not prove every external reader understands the format. The sender and receiver still need the same contract. A successful local conversion is one part of that review.

The missing-date row expects NULL in every formatted column and in the round-trip indicator. It remains missing rather than becoming an empty label or a default calendar date. The result makes that policy visible.

Format at the boundary rather than inside date logic

A date comparison or date calculation should usually keep the typed value. Formatting is useful for an export or a human-facing display. Those stages answer different questions.

Do not sort a report chronologically by an arbitrary month-first display string. Component order affects text sorting. Keep the original date available when chronological order matters.

The selected styles contain numeric components only. This sample does not depend on a localized month name. It also does not claim that every available style is suitable for every parsing target.

Keep the complete chosen format within the target width. Compare the compact round trip with the original typed date. A successfully formatted string still needs the receiver’s agreed interpretation.

When adapting the sample, keep an ambiguous day-and-month pair and a missing date in the tests. Match target width to the full chosen representation. That catches assumptions hidden by a single convenient date.

Format at the edge, keep the date as a date, and you are done.

A date display is not the date’s data type, it is text following a chosen format.

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