SQL: date type column with only mm-dd-CodePudding

I want to create a column of data type having only 'mm-dd' values. Is it possible and if yes how should I do it?

Note: Instead of "2022-06-07", I want "07-06"

CodePudding user response：

There is no date type that can store that format - in fact none of the date types store a date and/or time in any of the formats you typically recognize.

For your specific requirement, that looks like a char(5) for the data type, but how you constrain it so that it will only accept valid date values, I have no idea. You'd think this would work:

CHECK (TRY_CONVERT(date, string_column   '-2022', 105) IS NOT NULL)

But what about leap years? February 29th is sometimes valid, but you've thrown away the only information that can make you sure. What a bunch of mess to store your favorite string and trust that people aren't putting garbage in there.

Honestly I would store the date as a date, then you can just have a computed column (or a column in a view, or just do this at query time:

d_slash_m_column AS CONVERT(char(5), date_column, 105)

Why not just in your query (or only in a view) say:

[output] = CONVERT(char(5), data_in_the_right_type, 105)

I'd personally stay away from FORMAT(), for reasons I've described here:

CodePudding user response：

You can use the SQL Server FORMAT function:

FORMAT(col1, 'dd/MM')

Check the demo here.