< Home
SQL server, Web development and Dates
Having been a freelancer for a while now I have had the opportunity to work with a lot of different types of professional; developers, hardware and software architects, project managers, business focused web consultants and business analysts. I try my best to remember as much as I can when I work with these guys and to depart as much of my experience and thinking, when it’s valuable! Occasionally some things stick in your mind and appear to be an epiphany. What I’m about to try and describe was one such thing!
SQL server, Web app’s and dates! For various reasons dealing with dates in a web app, especially when there is a need to store these dates in SQL server has always presented challenges. Most of the time I’d use functions and sometimes date / time utility classes to do conversion and calculations on any date operations that I needed. There was always the issue of ‘the start of the day’ and ‘the end of the day’ finding a specific say of the month, finding a specific weekday in the month etc. Whilst solutions weren’t always the most difficult things to think about or code, it was always a difficulty to think of how best to approach this on a project by project basis.
A while back, I was working at a company that had a serious amount of professional development expertise, but the lack of database expertise was showing, so they employed a new DBA. Luckily, the new employee, that was selected to fill the void, sat next to me.
Whilst trying to build a time base scheduling web application I asked for some advice from Richard (the guy in the seat next door). Without mentioning the specifics of what I needed, Richard presented a solution similar to the following (I’ve adapted it over the last little while) that creates a table within SQL server that simply stores dates and any ‘date relevant’ information in a table that can be selected, joined on, or whatever.
At first I was sceptical, even to the point that I thought that this guy hadn’t understood what I was asking. ‘I create tables in a database to store data that might change, not to store static data!’ I thought. But after a few demonstrations it was obvious how useful using this technique was.
The crux of the solution was simply to create a dates table (or view if you prefer) in your database and add other columns that were pertinent to the kind of information that you were going to regularly use, in my case the day of the week or the day of the month etc. Then write some SQL User defined functions that assisted you.
The advantage of this approach, over doing this in your business object layer or even in your presentation layer, is that you create some static data that can be selected from the database rather than calculating at runtime. Using some cleverly constructed functions or stored procedures you can also create variations of your dates data quickly and easily.
The examples that follow are intended to give you an idea of the approach only but should give a good grounding. It’s best to implement the solution in a way that is pertinent to the uses of your application. Remember that your table is redundant, static data so you could add to it or delete from wit where necessary.
The table to use for most of your stuff:
CREATE TABLE [dbo].[_date] (
[appdateid] [INT] IDENTITY ( 1,1 ) NOT NULL,
[appday] [INT] NOT NULL,
[appmonth] [INT] NOT NULL,
[appyear] [INT] NOT NULL,
[dayofweekname] [VARCHAR](50) NOT NULL,
[dayofweek] [INT] NOT NULL,
[weekofmonth] [INT] NOT NULL,
[datetojoin] [DATETIME] NOT NULL,
CONSTRAINT [pk_appdate] PRIMARY KEY CLUSTERED ( [appdateid] ASC ) WITH ( pad_index = off,
statistics_norecompute = off,
ignore_dup_key = off,
allow_row_locks = on,
allow_page_locks = on ) ON [primary])
ON [primary]
SQL to populate the _date table (change the year values, in RED,
to populate the table with years worth of days accordingly).
DECLARE @Counter INT
DECLARE @StartDate DATETIME
DECLARE @DayCounter DATETIME
SET @Counter = 0
SET @StartDate = Dateadd(yyyy,-1,Getdate())
SET @DayCounter = @StartDate
WHILE (@DayCounter < Dateadd(yyyy,11,@StartDate))
BEGIN
INSERT INTO [dbo].[_date]([appday],
[appmonth],
[appyear],
[dayofweek],
[dayofweekname],
[weekofmonth],
[datetojoin])
VALUES (Datepart(dd,@DayCounter),
Datepart(mm,@DayCounter),
Datepart(yyyy,@DayCounter),
CASE Datepart(dw,@DayCounter) - 1 WHEN 0 THEN 7
ELSE Datepart(dw,@DayCounter) - 1END,
Datename(dw,@DayCounter),
dbo.Fnweekofmonth(@DayCounter),
dbo.Fngetdatefromparts(Datepart(dd,@DayCounter),Datepart(mm,@DayCounter),
Datepart(yyyy,@DayCounter)))
SET @Counter = @Counter + 1
SET @DayCounter = Dateadd(d,@Counter,@StartDate)
END
A function to return the end of day (one second before)
CREATE FUNCTION [dbo].[fn_GetEndOfDay](@InDate DATETIME)RETURNS DATETIME
AS
BEGIN
DECLARE @OutDate DATETIME
DECLARE @NextDay DATETIMESET @NextDay = Dateadd(DAY,1,@InDate)
SET @NextDay = dbo.Fn_returndatefromparts(DAY(@NextDay),MONTH(@NextDay),YEAR(@NextDay))
SET @OutDate = Dateadd(SECOND,-1,@NextDay)
RETURN @OutDate
END
[Instant SQL formatting tool used with this article, thanks] Lewis Barclay, errumm.co.uk 26/01/2008