<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

DateSerial Function for Microsoft SQL Server

Published on
32,232 Points
25,832 Views
4 Endorsements
Last Modified:
Approved
Community Pick
Kevin Cross
Father, husband and general problem solver who loves coding SQL, C#, Salesforce Apex or whatever.
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
;

Open in new window

Here is the usage:
SELECT dbo.DateSerial(2009, 4, 1), dbo.DateSerial(2009, -1, 1)
;

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
;

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)
;

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)
;

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)
;

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
4
Comment
Author:Kevin Cross
2 Comments
LVL 61

Author Comment

by:Kevin Cross
As an added resource, here is a nice reference on the SQL date and time data types:

Date and Time Functions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms186724.aspx

As you can see in SQL 2008 and higher, you now have Date and DateTime2 which support a broader range of valid dates.  Still no DateSerial function, so this is still valid.

Thanks again for reading and voting above.

Kevin
0
LVL 72

Expert Comment

by:Scott Pletcher
I think this can be done in a "sargable" way that is also less complex/convoluted.


WHERE (PostYear = YEAR(GETDATE()) AND --current year
               PostMonth BETWEEN MONTH(GETDATE()) - 3 AND MONTH(GETDATE()))
      OR  (MONTH(GETDATE()) < 4 AND ( --prior year, if applicable
              PostYear = YEAR(GETDATE()) - 1 AND
              PostMonth BETWEEN MONTH(DATEADD(MONTH, -3, GETDATE())) AND 12))
0

Featured Post

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Join & Write a Comment

Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month