SQL Function to Determine Day Type (Work Day vs. Week Day)

Here is a little handy function which I use to either determine day type in some ad hoc query or populate date dimension in the data warehouse via SQL Server Integration Services (on that see the upcoming posts). All I do is pass the date parameter (in a “datetime” data type) to determine if the date is classed as a work day or weekend. It’s a really neat and simple solution as you don’t have to construct the code to determine the field value as part of the larger query; all it takes is just a simple function call. The code behind the solution as well as an execution sample is below:

--------------------------------------------------------------------------------------
--             CREATE A SCALAR-VALUED FUNCTION TO DETERMINE DAY TYPE CODE
--------------------------------------------------------------------------------------
CREATE FUNCTION [dbo].[Day_Type_Code]
(@Date datetime)

RETURNS varchar (10)
AS
    BEGIN
             DECLARE @dtfirst int
             DECLARE @dtweek int
             DECLARE @iswkday varchar(10)

             SET @dtfirst = @@datefirst - 1
             SET @dtweek = DATEPART(weekday,@date)-1

                   IF(@dtfirst + @dtweek)%7 not in (5,6)
                        SET @iswkday = 'WD'
                   ELSE
                        SET @iswkday = 'WE'
    RETURN @iswkday
END

GO

Submit Article :- BlinkList + Blogmarks + Digg + Del.icio.us + Ekstreme Socializer + Feedmarker + Furl + Google Bookmarks + ma.gnolia + Netvouz + RawSugar + Reddit + Scuttle + Shadows + Simpy + Spurl + Technorati + Unalog + Wink

http://scuttle.org/bookmarks.php/pass?action=add

Posted in: SQL

Tags: ,

This entry was posted on Wednesday, January 25th, 2012 at 4:33 am and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply