An infinite date table

Here is the creation script for a neat function that will generate a table full of every date in the given range.


-- =============================================
-- Author:  Aaron D. Wells
-- Create date: 9/23/2010
-- Description: Get a list of dates in a range
-- =============================================
CREATE FUNCTION fn_Dates (@fromDate date, @toDate date)
RETURNS @dateTable TABLE( [Date] Date NOT NULL, FiscalYear int NOT NULL, FiscalMonth int NOT NULL )
AS 
BEGIN
     WITH CTE_DatesTable([date])
     AS (
     SELECT @fromDate AS [date]
          UNION ALL
          SELECT DATEADD(dd, 1, [date]) FROM CTE_DatesTable
          WHERE DATEADD(dd, 1, [date]) <= @toDate)  
          INSERT @dateTable
               SELECT [date],
                    CASE WHEN MONTH(date) > 9 THEN YEAR(date) + 1 ELSE YEAR(date) END FiscalYear,
                    CASE WHEN MONTH(date) > 9 THEN MONTH(date) - 9 ELSE MONTH(date) + 3 END FiscalMonth
               FROM CTE_DatesTable OPTION (MAXRECURSION 0)
     RETURN 
END
Using the function is pretty simple, just give it a starting and ending date. By the way, the FiscalYear column is used to calculate the fiscal year for the company I was working with when i wrote this. Your mileage may vary.

Comments

Post a Comment

Popular posts from this blog

ATLAS AJAX Control Toolkit AutoCompleteExtender with Key Lookup

I like the ATLAS AJAX Control Toolkit. It integrates with Visual Studio and has useful if not basic javascript functions that can help a website be much more interactive. The AutoComplete Extender is used to provide auto-complete functionality to a text box. To use it, you simply add an extender and a web service method that provides a list of strings based on the text that the user started typing. So, if you are trying to provide the exact string that the user is typing (ala google search help), you can use it out of the box. However, sometimes you want more list-ish functions, that is, you want to present the user with a list of valid choices that represent objects, not just the descriptions. One alternative is to use a Listbox with a ListSearch Extender. This extender is used to find an existing value within the listBox. The downside to this approach is that every item you are looking for has to be in the list. This can be rather unwieldy if the list has more than a couple dozen i...
Read more

Database Projects, SQL Unit Tests, and TeamCity

Image
Continuous Integration  is a really good idea, especially when you are working with a team of developers. There are lots of tools out there that can help your team build better quality into your application. TeamCity by JetBrains is one such tool that has been around for awhile; specifically, it is the one that my company uses. Visual Studio has Database Projects   SQL Server Data Tools  to address some of the inconsistencies that developers face when using a database as a storage layer; Things like version control and consistent deployment. It also includes SQL unit tests , which fills a huge gap if any  of your logic is in the database. Many organizations moved away from logic in the database years ago because it was tricky (at best) to verify logic on the data tier, of course, they were also making changes directly to the database itself... but I digress. If you are using SQL Server Data Tools, and run your unit tests locally, it is a joy to behold. Your data...
Read more

Accessing a ServiceStack RESTful API using jQuery with CORS

The following applies to jQuery version 2.0.3 and ServiceStack version 3.9.59 My latest project is using ServiceStack.net  to create a REST-ful API for an existing data interchange format. There are a few gotchas that are important to understand: REST API DELETE Methods and jQuery The specification may be a bit vague, but most server frameworks (including ServiceStack) ignore any parameters passed to DELETE methods in the body. Only parameters passed on the URL are sent into the method. This behavior is similar to a HTTP GET. That is okay, but when you call an $.ajax method using jQuery, it doesn't serialize and attach the data to the URL, but instead puts it in the body...where the server promptly ignores it. In order to handle this apparent disconnect, and use the same semantics in $.ajax, you need to move the parameters from the body into the URL. The following ajax prefilter code does the trick: $.ajaxPrefilter(function (options, originalOptions, jqXHR) { if (options...
Read more