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:<\/p>\n
\r\n--------------------------------------------------------------------------------------\r\n-- CREATE A SCALAR-VALUED FUNCTION TO DETERMINE DAY TYPE CODE\r\n--------------------------------------------------------------------------------------\r\nCREATE FUNCTION [dbo].[Day_Type_Code]\r\n(@Date datetime)\r\n\r\nRETURNS varchar (10)\r\nAS\r\n BEGIN\r\n DECLARE @dtfirst int\r\n DECLARE @dtweek int\r\n DECLARE @iswkday varchar(10)\r\n\r\n SET @dtfirst = @@datefirst - 1\r\n SET @dtweek = DATEPART(weekday,@date)-1\r\n\r\n IF(@dtfirst + @dtweek)%7 not in (5,6)\r\n SET @iswkday = 'WD'\r\n ELSE\r\n SET @iswkday = 'WE'\r\n RETURN @iswkday\r\nEND\r\n\r\nGO\r\n<\/pre>\n
![\"\"](\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2012\/01\/SQL_Results_fun_DayTypeCode.png\" "\\"SQL_Results_fun_DayTypeCode\\"")
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"