DateSerial Function for Microsoft SQL Server: SQL, SQL 2005, SQL 2008, SQL 2000, Date Functions, User Defined Functions, DateSerial, UDF

If you are a fan of DateSerial() function in Microsoft Access or like me you deal with data where you only have year and month but would like to do date calculation/manipulation, then here is a replication of this functionality for Microsoft SQL Server utilizing a user defined function.

DateSerial() takes in a number of years, months, and days and returns the appropriate date allowing you vary inputs and use negatives without having to worry about crossing over months or years. If you want more information on how the DateSerial() function worked within Access, see reference below.

Others may exist, but let's explore my version...

The Methodology

The main principle is to utilize simple DateAdd() function. The DateAdd() function takes a datepart like "day", "month", or "year", etc. without the quotes (unlike MS Access); a signed integer indicating the number or days to add/subtract; the datetime to add/subtract number of datepart units from.

Basically, once a date is established by parsing the year sent to our DateSerial() function, we can simply add months and days using DateAdd().

This is very straight-forward and explained in code, so...

The Code

Adds some error handling for invalid start years has been added that can be adjusted per your own tastes.

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(@year * 10000 + 101 AS char(8))
   ;
      
   -- 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
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

Select all Open in new window

Here is the usage:

SELECT dbo.DateSerial(2009, 4, 1), dbo.DateSerial(2009, -1, 1)
;

1:
2:

Select all Open in new window

Example usage above returns "2009-04-01 00:00:00.000" and "2008-11-01 00:00:00.000", respectively.

The first example, dbo.DateSerial(2009, 4, 1), simply puts in a year, month, and day. However, the second is a bit more involved.

For the second example, we start with the year 2009. Now for month, we have a -1, but what are we starting with? Well, since January is month 1, then a month of 0 would be one month prior to January; therefore, a year of 2009 and month of 0 would start us at December 2008. Now subtracting one month, we arrive at November 2008. The last parameter sets the day to be the 1st; hence our result.

Clear as mud, right!

Well, here is a look at a scenario where this may be useful:

We have a labor entry system that stores a year and month of entry that we cannot change, but we need to utilize the two values as a date to make our where clause more easily able to determine entries for the last three months. This is possible, otherwise, but can sometimes be ugly in syntax.

The below snippet is one solution I have used...

SELECT
   WorkOrder ,
   PostYear ,
   PostMonth ,
   RunTimeHours
FROM
   WorkOrderLaborEntry
--WHERE {date is three months ago or newer}
WHERE ( 
   (YEAR(GETDATE())-PostYear)*12 
   + ( MONTH(GETDATE()) - PostMonth ) 
) BETWEEN 0 AND 3
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select all Open in new window

Now here is the same code with DateSerial...

SELECT
   WorkOrder ,
   PostYear ,
   PostMonth ,
   RunTimeHours
FROM
   WorkOrderLaborEntry
--WHERE {date is three months ago or newer}
WHERE
   DATEDIFF(MONTH , 
      dbo.DateSerial(PostYear , PostMonth , 1),
      GETDATE()) BETWEEN 0 AND 3)
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select all Open in new window

Notice we were able to use standard date function as our DateSerial() tool returns a valid datetime value.

To explore this further, see this alternative allowing us to compare dates:

SELECT
   WorkOrder ,
   PostYear ,
   PostMonth ,
   RunTimeHours
FROM
   WorkOrderLaborEntry
--WHERE {date is three months ago or newer}
WHERE
   dbo.DateSerial(PostYear , PostMonth , 1) >= 
      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
   AND dbo.DateSerial(PostYear , PostMonth , 1) < 
      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())+1, 0)
;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Select all Open in new window

Very useful.

As a good alternative, my method without DateSerial() shown earlier is very clean as well for this particular case and was developed after creating this function, but as with anything else there is always a more complex need where this can come in handy like:

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

1:
2:

Select all Open in new window

This will return the date 35 days prior to the first day of the current month in the current year!

Thanks for reading, and I hope you find my ramblings useful. Always good to have a lot of tools in the arsenal, so just giving you another with our new found DateSerial() function.

Happy coding!

Best regards,

Kevin (aka MWVisa1)

References / Related Articles:
DateSerial in Access > http://office.microsoft.com/en-us/access/HA012288131033.aspx
TimeSerial in MS SQL > http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_669-TimeSerial-Function-for-Microsoft-SQL-Server.html

DateSerial Function for Microsoft SQL Server

SQL

SQL 2005

SQL 2008

SQL 2000

Date Functions

User Defined Functions

DateSerial

UDF

MS SQL Server

About The Author

More Articles From mwvisa1

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server Experts