Hot Dates

Dates (or rather Datetimes) are cause of much concern to new SQL server users and even sometimes to experienced users. While haunting some SQL server forums I came across a couple of ideas that I felt could be incorporated into one.

Firstly there is a function to create a date table in SQL Server 2000 here combined with a procedure to create a list of UK holiday dates as demonstrated here we clearly have a powerful tool.

In my case I wanted dates based on the financial year so I added some columns to handle that. I also wanted the number of working days between 2 dates and so I have amalgamated the data above to facilitate that.

Here is the complete code


-- Code taken from
-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519&whichpage=1
-- for date table





if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[F_TABLE_DATE]')
and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[F_TABLE_DATE]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function dbo.F_TABLE_DATE
(
@FIRST_DATE datetime,
@LAST_DATE datetime
)
/*
Function: dbo.F_TABLE_DATE

This function returns a date table containing all dates
from @FIRST_DATE through @LAST_DATE inclusive.
@FIRST_DATE must be less than or equal to @LAST_DATE.
The valid date range is 1754-01-01 through 9997-12-31.
If any input parameters are invalid, the fuction will produce
an error.

The table returned by F_TABLE_DATE contains a date and
columns with many calculated attributes of that date.
It is designed to make it convenient to get various commonly
needed date attributes without having to program and test
the same logic in many applications.

F_TABLE_DATE is primarily intended to load a permanant
date table, but it can be used directly by an application
when the date range needed falls outside the range loaded
in a permanant table.

If F_TABLE_DATE is used to load a permanant table, the create
table code can be copied from this function. For a permanent
date table, most columns should be indexed to produce the
best application performance.


Column Descriptions
------------------------------------------------------------------


DATE_ID
Unique ID = Days since 1753-01-01

DATE
Date at Midnight(00:00:00.000)

NEXT_DAY_DATE
Next day after DATE at Midnight(00:00:00.000)
Intended to be used in queries against columns
containing datetime values (1998-12-13 14:35:16)
that need to join to a DATE.
Example:

from
MyTable a
join
DATE b
on a.DateTimeCol >= b. DATE and
a.DateTimeCol < example =" 2005" example =" 20052" example =" 200511" example =" 2005364" example =" 4" example =" 11" example =" 362" example =" 31" sun="1," mon="2," tue="3," wed="4," thu="5," fri="6," sat="7" example =" 2005" example =" 2005" example =" 2005" example =" 2005" example =" Q1" example =" Mar" example =" September" example =" Tue" example =" Wednesday" example =" 2004-02-29" example =" 200403" example =" 52" mon="1," tue="2," wed="3," thu="4," fri="5," sat="6," sun="7" example =" 2004-W52" example =" 2004-W52-2" example =" 2004/02/29" example =" 2004/2/9" example =" 06/05/2004" example =" 6/5/2004" example =" Jan" example =" September" example =" 06/05/97" example =" 6/5/97" cr =" char(13)+Char(10)" errormessage =" '@FIRST_DATE" errormessage =" '@LAST_DATE" errormessage =" '@FIRST_DATE" first_date =" '+"> '99971231' begin
select @ErrorMessage =
'@LAST_DATE cannot be after 9997-12-31'+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
goto Error_Exit
end

-- Verify @FIRST_DATE is not after @LAST_DATE
if @FIRST_DATE > @LAST_DATE
begin
select @ErrorMessage =
'@FIRST_DATE cannot be after @LAST_DATE'+
', @FIRST_DATE = '+
isnull(convert(varchar(40),@FIRST_DATE,121),'NULL')+
', @LAST_DATE = '+
isnull(convert(varchar(40),@LAST_DATE,121),'NULL')
goto Error_Exit
end

-- Set @START_DATE = @FIRST_DATE at midnight
select @START_DATE = dateadd(dd,datediff(dd,0,@FIRST_DATE),0)
-- Set @END_DATE = @LAST_DATE at midnight
select @END_DATE = dateadd(dd,datediff(dd,0,@LAST_DATE),0)
-- Set @LOW_DATE = earliest possible SQL Server datetime
select @LOW_DATE = convert(datetime,'17530101')

-- Find the number of day from 1753-01-01 to @START_DATE and @END_DATE
select @start_no = datediff(dd,@LOW_DATE,@START_DATE) ,
@end_no = datediff(dd,@LOW_DATE,@END_DATE)

-- Declare number tables
declare @num1 table (NUMBER int not null primary key clustered)
declare @num2 table (NUMBER int not null primary key clustered)
declare @num3 table (NUMBER int not null primary key clustered)

-- Declare table of ISO Week ranges
declare @ISO_WEEK table
(
[ISO_WEEK_YEAR] int not null
primary key clustered,
[ISO_WEEK_YEAR_START_DATE] datetime not null,
[ISO_WEEK_YEAR_END_DATE] Datetime not null
)

-- Find rows needed in number tables
declare @rows_needed int
declare @rows_needed_root int
select @rows_needed = @end_no - @start_no + 1
select @rows_needed =
case
when @rows_needed < rows_needed_root =" convert(int,ceiling(sqrt(@rows_needed)))" number =" 0" number =" a.NUMBER+(16*b.NUMBER)+(256*c.NUMBER)" number =" a.NUMBER+(@rows_needed_root*b.NUMBER)" iso_start_year =" datepart(year,dateadd(year,-1,@start_date))" iso_end_year =" datepart(year,dateadd(year,1,@end_date))" number =" NUMBER+@iso_start_year" sunday =" 1," monday =" 2," saturday =" 7" sunday =" 1," monday =" 2," saturday =" 7" number =" NUMBER+@start_no" error =" convert(int,@cr+@cr+" name =" N'DATEINFO'" type =" 'U')" date =" dateadd(dd,datediff(dd,0,@DATE),0)" financial_year_start_date =" dateadd(dd,datediff(dd,0,@FINANCIAL_YEAR_START_DATE),0)" financial_year_start_date =" DATEADD(yy,"> 0
BEGIN
SET @YEAR = YEAR(@FINANCIAL_YEAR_START_DATE) -1
END
ELSE
BEGIN
SET @YEAR = YEAR(@FINANCIAL_YEAR_START_DATE)
END

RETURN (@YEAR)
END


-- populates Financial year start year
UPDATE DATEINFO
SET FINANCIAL_YEAR_START_YEAR = dbo.FINANCIAL_YEAR([DATE],'20000401')

-- note that could use YEAR(FINANCIAL_YEAR_START_DATE) if that has been populated

-- populates financial year start date
UPDATE DATEINFO
SET FINANCIAL_YEAR_START_DATE =
CASE
WHEN DATEDIFF(dd,DATEADD(yy, year([DATE]) - 1960, '19600401'),[DATE]) < financial_year_name =" RIGHT(CONVERT(VARCHAR(4)," weekend =" CASE" day_of_week =" 7)" day_of_week =" 1)" topic_id="49711" topic_id="45689)" a =" @YEAR%19" b =" @YEAR" c =" @YEAR%100" d =" @B" e =" @B%4" f =" (@B" g =" (@B" h =" (" i =" @C" k =" @C%4" l =" (32" m =" (@A" o =" 22"> 31
BEGIN
SET @R = @O - 31 + 400 + @YEAR * 10000
END
ELSE
BEGIN
SET @R = @O + 300 + @YEAR * 10000
END

RETURN @R
END
GO
--END fnDLA_GetEasterdate

-- SET EASTER DATES

UPDATE DATEINFO
SET UK_HOLIDAY = 'B'
--------------------------------NEW YEAR-------------------------------------------
WHERE DATE IN (SELECT MIN(DATE) FROM DATEINFO
WHERE MONTH = 1 AND DAY_OF_WEEK NOT IN (1,7)-- using American DATEFIRST
GROUP BY YEAR)
--------------------EASTER Good Friday and Easter Monday ------------------------------
OR DATE = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate([YEAR]))) --Good Friday
OR DATE = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate([YEAR]))) --Easter Monday
---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------
OR DATE IN (SELECT MIN(DATE) FROM DATEINFO
WHERE MONTH = 5 AND DAY_OF_WEEK = 2 -- using American DATEFIRST
GROUP BY YEAR)
OR DATE IN (SELECT MAX(DATE) FROM DATEINFO
WHERE MONTH = 5 AND DAY_OF_WEEK = 2 -- using American DATEFIRST
GROUP BY YEAR)
--------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------
OR DATE IN (SELECT MAX(DATE) FROM DATEINFO
WHERE MONTH = 8 AND DAY_OF_WEEK = 2 -- using American DATEFIRST
GROUP BY YEAR)
--------------------XMAS(Move to next working day if on Sat/Sun)--------------------
OR DATE IN (SELECT CASE WHEN DAY_OF_WEEK IN (1,7) THEN -- using American DATEFIRST
DATEADD(dd,+2,DATE) ELSE DATE END
FROM DATEINFO
WHERE MONTH = 12 AND DAY_OF_MONTH IN (25,26))


-- Add if a UK working day
UPDATE DATEINFO
SET UK_WORKING_DAY = CASE
WHEN (WEEKEND = 'Y') OR (UK_HOLIDAY = 'B') THEN 'N'
ELSE 'Y'
END


-- Now work out the next working day
-- NOTE THAT this fails for the last day
--

-- CREATE TEMPORARY TABLE for NEXT WORKING DATE

-- DROP TABLE #NextWorkingDate

CREATE TABLE #NextWorkingDate
(
DATE DATETIME NOT NULL,
NEXT_WORKING_DATE DATETIME NULL
)

INSERT #NextWorkingDate(DATE,NEXT_WORKING_DATE)
SELECT d1.DATE,
NEXT_WORKING_DATE = (SELECT MIN(d2.DATE) FROM DATEINFO d2 WHERE d2.UK_WORKING_DAY = 'Y' AND D2.DATE > d1.DATE)
FROM DATEINFO d1
GROUP BY d1.DATE

-- Do update
UPDATE DATEINFO
SET NEXT_UK_WORKING_DATE = n.NEXT_WORKING_DATE
FROM dateinfo t inner join #NextWorkingDate n
On t.date = n.DATE

GO

So we can now trivially calculate the number of working days between 2 dates using

DECLARE @START DATETIME
DECLARE @END DATETIME

SET @START = '20000109'
SET @END = '20000116'

SELECT COUNT(1) from DATEINFO
WHERE UK_WORKING_DAY = 'Y'
AND DATE > @START and DATE <= @END

This can be used to calculate all sorts of things. Essentially if we think of it in more abstract terms it is calculating the number of events between 2 dates. We don't have to use Count as I needed to - we can use pretty much any mathematical operator. All we need to do is populate the events (UK_WORKING_DAY here) and then adapt the code above accordingly
.