|
Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
|
|
Returning a week number for any given date and starting fiscal monthBy Paul Alcon on 2 May 2007 | Tags: Queries Paul writes "Sql Server comes with a host of built in functions such as ISNULL, CONVERT and CAST. Now if that wasn't enough rope to hang ourselves with, as of Sql Server 2000 we gained the ability to create our own user defined functions. In this article I will be looking at the three main date functions DATEADD, DATEPART and DATEDIFF (there is a fourth called DATENAME but I want to get to the end of this article before you fall asleep so I decided to leave it for another date and time! And no it doesn't foretell the name of your future blind date so it's not as interesting as it sounds anyway) Then I will be combining all three in a user defined function of our own by which time our necks will be well and truly stretched!" Sql Server comes with a host of built in functions such as ISNULL, CONVERT and CAST. Now if that wasn't enough rope to hang ourselves with, as of Sql Server 2000 we gained the ability to create our own user defined functions. In this article I will be looking at the three main date functions DATEADD, DATEPART and DATEDIFF (there is a fourth called DATENAME but I want to get to the end of this article before you fall asleep so I decided to leave it for another date and time! And no it doesn't foretell the name of your future blind date so it's not as interesting as it sounds anyway) Then I will be combining all three in a user defined function of our own by which time our necks will be well and truly stretched! Firstly it is useful to know about how dates are stored in Sql Server and why it is that way. All dates of datetime data type in Sql Server are based on the Gregorian calendar which was adopted by Britain and it's American colonies midway through the year of 1752. Therefore, the earliest date that can be stored as a datetime in Sql Server is 1st January 1753 anything prior to that involves dealing with a crossover from Julian, Chinese and who knows what else, so if you fit into this category you will have to implement your own method for date manipulation and you will have a very long neck indeed. All dates are stored as two 4 byte integers. The first 4 bytes represent the number of days prior or since 1st January 1900, and the remaining 4 bytes represent a count of 300ths of seconds since midnight. So with that in mind lets look at the DATEADD function first. This can be used to add a positive or negative interval of time to an existing datetime value and return a new one. It takes three arguments, the first argument is the interval which can be any one of the following : INTERVAL ABBREVIATED
The second argument is the number of intervals to add and can be a negative or positive integer. The third argument is the datetime to which the interval(s) are to be added. An example of it's usage is demonstrated here: SELECT DATEADD(day, 1, getdate()) AS [Tomorrow] Pretty simple stuff so far, lets move onto DATEPART. This fellow returns an integer that represents a part of a datetime value, for example the month part of 1st January 2007 is 1, because January is the first month of the year. It takes two arguments, the part of the date you want, and the datetime value you want it from. Valid date parts are the same as the intervals listed above for the DATEADD function. And I would be negligent if I didn't give you an example so here goes, this will give you the day of the current month: SELECT DATEPART(day, getdate()) AS [Today is:] Alright so hopefully you're still with me and your necks are in tact, cool. Don't worry it's going to get more interesting real soon I promise. We've got just one more function to look at today, DATEDIFF this is very simple it gives you the count of intervals between two dates. It takes three arguments being the interval you're interested in and two dates. Things to watch out for are if you put the more recent date first you will get a negative result, for example: SELECT DATEDIFF(day, '20070413 00:00:00.000', '20070412 00:00:00.000') result -------------------------- -1 Where as: SELECT DATEDIFF(day, '20070412 00:00:00.000', '20070413 00:00:00.000') results --------------------------- 1 The other thing to be aware of is rounding: SELECT DATEDIFF(day, '20070412 00:00:00.000', '20070413 23:59:59.999') result --------------------- 2 Believe me some people find that surprising! So that's all great stuff, but how's this going to benefit us in the real world. Well amongst many other uses the date functions are very handy when we need to group sets of data for aggregation such as average, count or sum. One I use particularly often is DATEPART to group time related data into weeks. This is fine but it always takes January 1st as the beginning of week 1, no matter what day of the week that happens to be, so you often get a week starting on an arbitrary day of the week with less than 7 days in it. Also sometimes my clients' fiscal year will run from some other month commonly April so I need week number 1 to be returned for April not January. So lets use this problem and solve it by combining the date functions we just looked at into a function of our own. Firstly we need to establish some basic rules
From this we can see for any given date all we need to do is:
So here is the TSQL code: create function FiscalWeek (@startMonth varchar(2), @myDate datetime) returns int as begin declare @firstWeek datetime declare @weekNum int declare @year int set @year = datepart(year, @myDate)+1 --Get 4th day of month of next year, this will always be in week 1 set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102) --Retreat to beginning of week set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek) while @myDate < @firstWeek --Repeat the above steps but for previous year begin set @year = @year - 1 set @firstWeek = convert(datetime, str(@year)+@startMonth+'04', 102) set @firstWeek = dateadd(day, (1-datepart(dw, @firstWeek)), @firstWeek) end set @weekNum = (@year*100)+((datediff(day, @firstweek, @myDate)/7)+1) return @weekNum end This is a scalar-value user defined function, because it will return a single value as a result. In the function we declare a variable @firstWeek that will ultimately hold the first day of the first week, and another called @weekNum that will be used to return our final calculated result. Firstly we concatenate the year of our passed in date+1 to the passed in starting month and day 4 as an argument to the convert function to get a valid datetime value that we know is somewhere in week one for that year. Next we use the DATEADD function to get to the first day of that week. If for example our date is a Wednesday(day number 3) and Monday is our first day of the week (day number 1) then 1 subtract 3 = -2 days = Monday. Now we need to check where our calculated first day of the year is in respect to the date that was originally passed into our function, we simply need to see if our passed in date is less than the first day of the year, if it proves to be so then it must belong to the previous years set of weeks. Otherwise it belongs to the current years set of weeks. If it is the former we need to recalculate our @firstWeek variable for the previous year and wind it back to Monday as before. Now we simply calculate the difference in days using the DATEDIFF function and divide that by 7 (seven days in a week) to this we add 1 (as integer division will be truncated we round up to include the partial week) Finally we put the year and week together (multiply year by 100 to make room for the week number and add it on) We include the year so that if your result set is more than a year in scope say 2004 - 2006 , week 5 for 2004 won't get grouped with week 5 in 2005 and 2006. And the week number is easily separated using modulus (example 200405 % 100 = 5 ) So the only thing not yet mentioned is how you establish which day if the week is day one. Luckily for us Sql Server has an option called DATEFIRST so we set that first and then we are free to call our function. Here is a very basic example of its usage in a select statement: SET DATEFIRST 1 -- Set the first day of the week to Monday
select dbo.FiscalWeek('04',getdate())
The '04' parameter is because in my experience April is the most common starting month, but you can pass in '01' to get ISO8601 week numbers, or whichever month number suits your needs. This function can be used in a group by clause also which is largely the point of this exercise, so if you have a datetime column you can group your result set into weeks: SELECT
SUM(ValueColumn),
dbo.FiscalWeek('04', DateColumn) yearAndWeekNum,
dbo.FiscalWeek('04', DateColumn)%100 justWeekNum
FROM
myTable
GROUP BY
dbo.FiscalWeek('04', DateColumn), dbo.FiscalWeek('04', DateColumn)%100
Well that's it, I hope you find this useful and thank you for reading my little contribution, and I hope your neck's still in tact after all that. |
- Advertisement - |