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 :)