Presents your  
SQL SERVER E-NEWSLETTER for November 18, 2003 
 
<-------------------------------------------> 
  
USE FN_JUSTDATE() TO FIX BETWEEN DATE1 AND DATE2 PROBLEMS   
  
Do you know why the following query produces interesting, albeit 
incorrect, results?   
  
SELECT * FROM MyTables   
WHERE DateCreated BETWEEN 1-Nov-03 AND 30-Nov-03   
  
Every row created on November 1 is included, yet no rows created on 
November 30 are included, because your DateCreated column is specified
as a DateTime type. Your application calls GetDate() to supply today's
date automatically.   
  
The reason why your query doesn't work is because the WHERE predicate 
doesn't include a time value. Therefore, SQL defaults its value to 
12:00:00, so your WHERE predicate, expressed precisely, is this:   
  
WHERE DateCreated BETWEEN '1-Nov-03 12:00:00' AND '30-Nov-03 12:00:00'  

  
If you systematically test this by writing INSERT statements designed to
expose the internal logic of SQL, you might find that some rows created 
on November 1 before 12:00:00 are excluded.   
  
There are a number of ways to fix this problem. The solution depends in 
part upon your logic. If your logic lies in the front-end application, 
then modify your code to append the correct time values. In the first case, 
add 00:00:00 to the date value; in the second case, add 23:59:59 to the 
date value. If your logic resides in the back end (as in a stored 
procedure), then perform your appends within the stored procedure.   
  
To simplify the process of solving these problems, I created a function 
called fn_JustDate(), which accepts a DateTime argument and strips off 
the time elements. Here's how I write my stored procedures:   
  
CREATE PROCEDURE ap_SalesBetween   
  
    (   
        @d1 DateTime,   
        @d2 DateTime   
    )   
  
AS   
    /* SET NOCOUNT ON */   
    SELECT * FROM Sales   
    WHERE DateEntered BETWEEN dbo.fn_JustDate(@d1) AND 
dbo.fn_JustDate(@d2)   
    RETURN   
  
These stored procedures rely on the user-defined function 
fn_JustDate(),

which is declared like this:   
  
CREATE FUNCTION dbo.fn.JustDate   
    ( @date datetime )   
RETURNS varchar(10)   
AS   
    BEGIN   
        RETURN ( CONVERT(varchar(10),@date,101) )   
    END   
  
Arthur Fuller has been developing database applications for 20 years. 
His experience includes Access ADPs, Microsoft SQL 2000, MySQL, and .NET.   
  
----------------------------------------