The YearMonth - a simple User-Defined Type

Dealing with data that relates to a particular year and month can cause problems in a database.

You can store it as a datetime but then you have to decide whether to use the first of the month or the last.  If you use the last then you need to work out how many days the month has every time you create a new row.  You also have to remember whether you chose the first or the last - getting this wrong can be disasterous.  And what if you want a unique constraint?  You can easily check whether the date is unique but if someone uses the time component or uses 30 Jul when someone else has used 31 Jul then you've got more problems.

A better solution would be to store it as two numbers (i.e. Year and Month columns).  This will avoid any confusion with times or days but what happens if you need all to return of the data later than Feb 2005?  You can't just query for Year > 2005 and Month > 2 - you'd never get anything for Jan.  If you use (Year * 100) + Month > 200502 then the SQL is simple but you can forget about using any indexes.

The best solution I've seen so far is to use a user-defined type (UDT).

UDTs are not without their problems .  The main disadvantage is that changing a UDT once it's used on a table can be time-consuming, especially if the type is widely used. Additionally, if you're using temporary tables, table variables or multiple servers then there are additional steps required to use UDTs.

One of the best books I've read on SQL Server is Pro SQL Server 2005 Database Design and Optimization by Louis Davidson .  He's not a fan of UDTs, but unfortunately my copy of the book was destroyed in a flood so I can't quote his reasons at the moment.

Having said that, I still think UDTs are the best solution to this problem.

To create the type itself, we use the following syntax:

EXEC sp_addtype YearMonth, int
GO

In SQL Server 2005 or 2008 you can use the alternative syntax, which gives you the option of specifying the schema:

CREATE TYPE dbo.YearMonth FROM INT
GO

At this point the YearMonth is simply a synonym for an int.  What we need are some rules to define our type:

CREATE RULE CheckYMYear
AS
    @YearMonth BETWEEN 190000 AND 300000
GO

CREATE RULE CheckYMMonth
AS
    CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR), 5, 2) AS INT) BETWEEN 1 AND 12
GO

The final step is to bind the rules to the UDT:

EXEC sp_bindrule 'CheckYMYear', 'YearMonth'
EXEC sp_bindrule 'CheckYMMonth', 'YearMonth'
GO

Now we're ready to use the type in a table: 

CREATE TABLE TestTable(
    TestTableID int NOT NULL,
    TestYearMonth YearMonth NOT NULL
)

Finally, the following two functions are useful if you ever need to convert between DateTime and YearMonth types:

CREATE FUNCTION fcGetYearMonthFromDate
(
    @Date DATETIME
)
RETURNS YearMonth
AS
BEGIN
    DECLARE @ResultYearMonth YearMonth

    SET @ResultYearMonth = YEAR(@Date) * 100 + MONTH(@Date)

    RETURN @ResultYearMonth
END
GO

CREATE FUNCTION fcGetDateFromYearMonth
(
    @YearMonth YearMonth,
    @EndOfMonth BIT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @ResultDate DATETIME
    DECLARE @YearMonthString CHAR(6)

    SET @YearMonthString = CAST(@YearMonth AS CHAR)   

    SET @ResultDate = CONVERT(DATETIME, @YearMonthString + '01', 112)   

    IF @EndOfMonth = 1
        SET @ResultDate = DATEADD(DAY, -1, DATEADD(MONTH, 1, @ResultDate))

    RETURN @ResultDate
END
GO