3

I want to create a column that would be something like this:

create table dbo.t1
(
    [Notification_Month] datepart(mm, [date]) null,
)

I want to store the month as a date property without storing other parts of the date. It's a weird question I know, but this table is being used by a WPF application so it would be easier to restrict date entries if SQL Server allowed this but if not I could probably find a long and convoluted solution using c#.

I have some other ideas for workarounds if this isn't possible but thought before I say it's not, always best to check with stack.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3486773
  • 1,174
  • 3
  • 25
  • 50

1 Answers1

9

Although you still have to store [date] as a DATE type, you can use a computed column:

Create Table yourTable(
     [NOTIFICATION_MONTH] DATE,
     , [MONTH_NOTIFIED] as DATEPART(MONTH, [NOTIFICATION_MONTH])
)

Add PERSISTED will save it in the table. Without it, it will be computed automatically each time.

On the MSDN CREATE TABLE page under computed_column_expression:

Is an expression that defines the value of a computed column. A computed column is a virtual column that is not physically stored in the table, unless the column is marked PERSISTED. The column is computed from an expression that uses other columns in the same table. For example, a computed column can have the definition: cost AS price * qty. [...]

Another option is:

  • Create a table with a tinyint for month
  • Create a view on your table with a date
  • Create an trigger on the view and replace date by month
  • Insert into the view with dates
Julien Vavasseur
  • 3,854
  • 1
  • 20
  • 29