CODEWITHSUNDEEP
sql-serverdatabasedatabase-design
Gainster
Gainster

Reputation: 5631

storing time and day of week

Challenge : I have a requirement in which I have to implement recurring events. I am storing day of the week , time and the date range for which the event will reoccur.

Possible solution: Storing time and day of week as string and enumeration. Storing current and setting the time and day for that day ?

Question : What is the best practice on storing time and day of week ?

EDT: I am using SQL Server Database

Upvotes: 1

Views: 2196

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

Another alternative is to have computed columns representing the parts that you're after.

CREATE TABLE dbo.foo
(
  bar DATETIME,
  bar_day_of_week AS DATEPART(WEEKDAY, bar),
  bar_time AS CONVERT(TIME, bar)
);

INSERT dbo.foo(bar) SELECT GETDATE();

SELECT bar, bar_day_of_week, bar_time FROM dbo.foo;

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271241

This best approach might dependson the database you are using. But, there are two general approaches, both quite reasonable.

The first is to store dates and times in the native format for the database. Most databases have functions to extract day of the week and time from a date time type. You would write your queries using these functions. Typically, you would store these as one field, but sometimes you might want to separate the date and time portions.

The second is to have a calendar table. A calendar table would have a date or dateid as a key, and then contain columns for what you want to know about it. Day of the week would be an obvious column.

A calendar table is the preferred solution in some situations. For instance, if you are going to internationalize your application, then being able to get day of the week from a table makes it easier to get the string English, Greek, Chinese, Russian, Swahili, or whatever your favorite language is. Or, if you want to keep track of specific holidays, then a calendar table can store this information as well. Or, if you have a business running on a strange financial calendar (such as 5-4-4), then a calendar table is a good choice.

In either case, you do not need to store redundant date information in the table. You should derive it, either from a built-in function or by looking up what you want in a reference table.

Upvotes: 2

Related Questions