How to break down smalldatetime into year, month, day indexes?

I am using SQL Server 2008.

I have some dates in my database that I "think" I want to break down into smaller parts. The dates are birthdays and death days. I want to be able to output them like by querying people who were born in October or on May 12th or in 1945.

I was told that a typical way of doing this is to take a date and break it into smaller pieces and put each piece of the date into its own column, like this:

2001-03-12 00:00:00 // EventDate column

Ad开发者_运维百科d these columns:

2001 // EventYear column
03 // EventMonth column
12 // EventDay column

First, is this a good way of doing this? If so, second, can I somehow have SQL Server automatically break the date part and put it into its own columns?

I'd appreciate ideas and solutions.

---

I would recommend that you leave it as a date column and then use DatePart in queries to filter results.

Select * from TABLEX
where DatePart(YEAR,EventDate) = 1945

---

It doesn't sound like the business requirement is very solidified. For what reason would you need to break out the different parts of the date? If you don't need to, then I wouldn't.

But, if you do find the necessity to do this then I'd utilize computed columns that are persisted. There wil lbe some overhead on an insert, but because there won't be any updates on existing data (your birthdate and death date won't change) then you won't see any performance overhead on a SELECT.

Something like this:

create table DateTest
(
    SomeDate datetime not null,
    SomeYear as datepart(yy, somedate) persisted,
    SomeMonth as datepart(mm, somedate) persisted,
    SomeDay as datepart(dd, somedate) persisted
)

---

Here is what I do.

I have a table "lib.Dates". It has a DATE as primary key.

It has additional columns with additional information to this date. This is for example day of month, day to end of month, week of year etc.

Joining this date table with dates allows me to: * Get a list of all dates (for example grouping sales per person by date would have no entry for zero sales, this way it can have) * Do funny things like all dates in week 23 of a year, which is normally harder to get.

This is part of a number of such tables that I have stored procedures maintain daily (-3 years, +5 years).