CREATE FUNCTION [dbo].[fnGetInitialDate] (
@Month int = null,
@Year int = null,
@CurrentDate datetime
)
RETURNS int
AS
BEGIN
Declare @ReportDate datetime
Declare @InitialMonth int
if @month IS null
set @month = Month(Dateadd(m, -1, @CurrentDate))
if @Year IS null
set @Year = Year(Dateadd(m, -1, @CurrentDate))
-- NORMALISE Date
/*
Select
@Date = StartDateTime,
@DateDiff = AgeInMonths
from
(
select top 1
StartDateTime,
AgeInMonths
from
dbo.<censored>
where
StartDateTime is not null
) TA
*/
set @ReportDate = Cast(@Year AS VARCHAR(4)) + '-' + Right('00' + Cast(@month AS VARCHAR(2)),2) + '-01'
set @InitialMonth = datediff(m, @CurrentDate, @ReportDate)
return @InitialMonth
END
This SQL Server function features:
- A name that doesn’t tell you anything ✔
- Comments indicating non-existent functionality ✔
- Big sections commented out with no explanation ✔
- Using strings for date arithmetic ✔
After much thought I concluded that this code is functionally equivalent to DATEDIFF(MONTH, GETDATE(), @ReportDate), except you can choose a different year or month for the current time.
This function is still at large on a production database server (names have been suppressed to protect the innocent). Developers are recommended not to approach it as it is considered dangerous, and extremely confusing.
April 21, 2009
