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