CODEWITHSUNDEEP
sql-serverdatetime
Earlz
Earlz

Reputation: 63895

Are these two pieces of SQL code the same?

I'm working with a bit of an older code base where conciseness is not found in many places.

One piece of code we are constantly using in the database is to determine if two dates are within the same program year. For instance, Program Year 2011 begins on July 1st, 2011 and ends on July 1st, 2012(or technically the day before)

The usual way I see that this problem is solved is by using this kind of code:

if Month(@EnrollmentDate)>=7 begin
  set @StartDate='07/01/'+LTRIM(RTRIM(Year(@EnrollmentDate)))
  set @EndDate='07/01/'+LTRIM(RTRIM(Year(@EnrollmentDate)+1))
end else begin
  set @StartDate='07/01/'+LTRIM(RTRIM(Year(@EnrollmentDate)-1))
  set @EndDate='07/01/'+LTRIM(RTRIM(Year(@EnrollmentDate)))
end
...
where (ENROLLMENTDATE >= @StartDate and ENROLLMENTDATE < @EndDate)

I recently happened to have to solve this problem and the instant thing that popped in my head was something much more concise:

where year(dateadd(mm,-6,ENROLLMENTDATE)) = year(dateadd(mm,-6,@EnrollmentDate))'

Before I go introducing new bugs into a system that "just works", I'd like to ask SO about this. Are these two pieces of code exactly the same? Will they always give the same output(assuming valid dates)?

Upvotes: 2

Views: 95

Answers (3)

Pondlife
Pondlife

Reputation: 16260

An even easier way to handle this situation is to create a calendar table with a column for the program year. Then there is no logic at all, you just query the values and compare them:

if 
(select ProgramYear from dbo.Calendar where BaseDate = @StartDate) = 
(select ProgramYear from dbo.Calendar where BaseDate = @EndDate)
begin
-- do something
end

There are many posts on this site about creating calendar tables and using them for many different purposes. In my experience, using a table in this way is always clearer and more maintainable than creating formulas in code.

Upvotes: 1

Neil
Neil

Reputation: 55402

As it happens, December has 31 days, so no matter how many months you need to subtract, you will always be able to align the resulting date range with a whole year, and therefore your expression will always be true whenever the original one was, even in the general case for an enrolment year starting on other dates.

I once used a dialect of SQL with a range of date manipulation functions that made this slightly easier than string twiddling, something like this:

WHERE enrolmentdate >= @YearBeg(:enrolmentdate + 6 MONTHS) - 6 MONTHS
  AND enrolmentdate < @YearBeg(:enrolmentdate + 6 MONTHS) + 6 MONTHS

Upvotes: 0

Rodolfo
Rodolfo

Reputation: 4183

the problem I see is that, depending on the optimizer, your solution (that looks better) may not use an index defined on ENROLLMENTDATE since you're doing operations on it, while the original solution would. If there are no indexes on that field, then I don't see an issue

Upvotes: 1

Related Questions