CODEWITHSUNDEEP
sqlsql-servert-sql
Christopher Kinyua
Christopher Kinyua

Reputation: 170

SQL column with calculate default value while creating table

I am trying to create a table with two columns, holiday_date and day_of_the_week.

I'd like the day_of_the_week column to be derived by default from the holiday_date column using the datepart() function. The day_of_the_week column is by default null and computed when new values of holiday_date are inserted.

I have tried using the query below:

CREATE TABLE [HolidaysTest] (
    [holiday_date] varchar(50),
    [day_of_the_week] int DEFAULT datepart(dW, holiday_date)
)

This isn't working and the error that results is shown below

The name "holiday_date" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

Upvotes: 0

Views: 1576

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

If you want day_of_the_week() to be in the table and always consistent with holiday_date, then I would recommend:

create table HolidaysTest (
    holiday_date date,
    day_of_the_week as (datepart(weekday, holiday_date))
);

Note the change of type of holiday_date. Do not store dates as strings! Use the built-in data types.

day_of_the_week is calculated when you refer to it in a query. It is always accurate.

Also not that weekday is spelled out. This generally makes queries less ambiguous and more maintainable.

Upvotes: 1

Neel Darji
Neel Darji

Reputation: 143

If you don't want to store day of week and you only want to return int value representing week of day then don't create column for it in table.

Just create your table with holiday_date and at time of fire select query write something like this:

select holiday_date, DATEPART(WEEKDAY, holiday_date) from HolidaysTest

Upvotes: 1

Related Questions