Feb012008

SQL Dynamic Date Range

Goog Feed, General, ColdFusion, SQL    Comments (0)


I work for a school district here in Texas and today an issue arose. We needed to run a nightly SQL job which pulls students from our Student Information System and dumps them into some tables we use for a local application. Problem is while we have about 22,000 active students our total student table has over 55,000 records in it, some from prior years.

The initial reaction was to hard-code the school year start and end dates into the system, but then if our DBA leaves, or we forget what kind of lame hack we put in place we have problems next school year.

I had done this previously in ColdFusion, but today I decided to do a SQL version of it. It will take the current day and decides what the current school year start and end dates are based on the current day, so we're always pulling current data only no matter what year it may be.

DECLARE @startDate datetime
DECLARE @endDate datetime

SET @startDate = cast('7/1/' + cast(datePart("yyyy",getDate()) AS varchar) AS datetime)
SET @endDate = cast('6/30/' + cast(datePart("yyyy",getDate()) AS varchar) AS datetime)

SET @startDate = CASE WHEN cast(@startDate AS datetime) > getDate() THEN cast(dateAdd("yyyy",-1,@startDate) AS datetime) ELSE cast(@startDate AS datetime) END
SET @endDate = CASE WHEN cast(@endDate AS datetime) < getDate() THEN cast(dateAdd("yyyy",1,@endDate) AS datetime) ELSE cast(@endDate AS datetime) END

SELECT @startDate AS startDate, @endDate AS endDate

The end result is 7/1/2007 as the startDate and 6/30/2008 for the endDate. It works perfect for us, and I hope it works for you.



Comments (0) | Print | Send | del.icio.us | Digg It! | Linking Blogs | 1141 Views

Related Blog Entries

Comments

There are no comments for this entry.

[Add Comment]