Thursday, January 07, 2010

User-Defined Functions for Today and Tomorrow in SQL

Just a quick post today. I have been working on some bugs on a system were about to release, which was originally built by some dodgy developer before we took it over. The older reaches of the application are a total mishmash f things that should be in the database, data access layer, business logic layer and in the UI, so please don't flame me with comments like "you shouldn't be doing this in the database, it should be in the ......".

One of the features that I was fixing was a view that contains the current effective price for a product. Imagine that you have a table that contains prices, and when a price changes you insert a new row with the updated price and an effective date. Simple stuff - the current price is the one with the most recent effective date that is either today or in the past, i.e. before tomorrow.

Now, SQL server is a pain in the ass when it comes to simple date stuff like this, because all I want is something like this:

SELECT
TOP 1 *
FROM
ProductPrice
WHERE
ProductID = @ProductID
AND EffectiveDate <>
ORDER BY
EffectiveDate DESC

I therefore wrote a couple of functions to achieve this:

CREATE FUNCTION [dbo].[Today]()
RETURNS datetime
AS
BEGIN
DECLARE @today datetime;

SET @today = convert(datetime, convert(varchar(10),getdate(),101), 101);

RETURN @today;
END

CREATE FUNCTION [dbo].[Tomorrow]()
RETURNS datetime
AS
BEGIN
DECLARE @tomorrow datetime;

SET @tomorrow = convert(datetime, convert(varchar(10),getdate(),101), 101);
SET @tomorrow = DATEADD(d, 1, @tomorrow);

RETURN @tomorrow;
END


Enjoy :)