SQL column with calculate default value while creating table

Question

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.

No I don't. I'd like it to return an integer value representing the day of the week depending on the date. — Christopher Kinyua
– Christopher Kinyua, Commented Sep 27, 2019 at 9:22
learn.microsoft.com/en-us/sql/relational-databases/tables/… — Dale K
– Dale K, Commented Sep 27, 2019 at 9:42

Gordon Linoff · Accepted Answer · 2019-09-27 11:08:57Z

1

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.

answered Sep 27, 2019 at 11:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dale K · Accepted Answer · 2019-09-27 10:10:48Z

1

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

edited Sep 27, 2019 at 10:10

Dale K

28.1k15 gold badges59 silver badges85 bronze badges

answered Sep 27, 2019 at 9:47

Neel Darji

1434 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL column with calculate default value while creating table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related