Crossed Logic: DateSerial In Microsoft SQL Server 2005

Well, in Group By Time: TimeSerial Makes A Return To Microsoft SQL 2005 we explored bringing TimeSerial functionality to SQL Server, but of course we can't stop there as the DateSerial function is pretty useful too.

There are probably a number of different methods to achieve this, but here is what I came up with.

Code listing:


CREATE FUNCTION dbo.DateSerial(@year int, @month int, @day bigint)
RETURNS datetime
AS
BEGIN
    DECLARE @date datetime

    -- catch invalid year entries and default appropriately
    SET @year = CASE
        WHEN @year < 1900 Then 1900
        When @year > 9999 Then year(getdate())
        Else @year End
    
    -- convert date by adding together like yyyymmdd
    SET @date = Cast(Cast(@year * 10000 + 101 As varchar) As datetime)
    -- Alternative method of parsing year into base date
    -- SET @date = Cast('1/1/' + Cast(@year As varchar) As datetime)

    -- Add to date the proper months subtracting 1 since we used 1 as start instead of zero.    
    SET @date = DateAdd(mm, @month - 1, @date)
    -- Add to date the proper days subtracting 1 since we used 1 as start instead of zero.    
    SET @date = DateAdd(dd, @day - 1, @date)

    RETURN @date
END

First line is to avoid errors in incorrect starting year value, but can be adjusted according to your own needs. The months and days are added in through simple DateAdd which allows for positive/negative numbers in addition to not being bound by 12 or 31 respectively making this like our TimeSerial function in that it can be used to simply convert a year, month, and day into date or to do some date math on the fly.

Usage:


SELECT dbo.DateSerial(YEAR(GETDATE()), MONTH(GETDATE()), 1 - 35) AS dateSerialized

This will return the date 35 days prior to the first day of the current month in the current year. Moreover, since this solution takes advantage of user defined function only, implementing in SQL 2000 should not be an issue.

So there you have it, DateSerial in SQL Server.

Happy coding!

References: