Using BETWEEN with dates

T-SQL has a handy BETWEEN statement that makes it easy to apply date range criteria. The basic usage is

WHERE dtSomeDate BETWEEN '1/1/2007' and '1/31/2007'

...which can replace the cumbersome

WHERE dtSomeDate >= '1/1/2007' AND dtSomeDate <= '1/31/2007'

either expression would (in theory) retrieve any date during January 2007. But SQL Server dates can contain a time component - so if dtSomeDate was actually '1/31/2007 08:00:00' meaning 8:00 AM on the 31st, that date actually would fall outside the desired range.

I picked up a tip from SqlServerCentral that removes the time - you could write
DATEADD(d,DATEDIFF(d,0,dtSomeDate),0)
and I even re-posted that tip at my web site, but every time I need to use it, I have to go look it up. So I wrote the following function that is easy to remember & can be invoked whenever comparing dates:


CREATE FUNCTION dbo.fnNoTime
(@DateTime datetime)
RETURNS datetime
AS
BEGIN
DECLARE @date smalldatetime
SET @date = DATEADD(d,DATEDIFF(d,0,@DateTime),0)
RETURN @date
END

...returning to my original example, you would use the function as follows:

WHERE dbo.fnNoTime(dtSomeDate) BETWEEN '1/1/2007' and '1/31/2007'

Works with Vista

There are a surprising numbers of free utilities that work with Windows Vista.

My faves are FoxIt Reader, a fast & free replacement for Adobe Reader; NotePad2, a smart NotePad replacement for programmers that color-codes text based on file extension; and Ad-Aware SE Personal, which scans for spyware & other such nasties.

Thus far, every PDF that I've opened with FoxIt has displayed just fine, without the long loading times (and frequent bloated updates) of Acrobat. The only downside is that sometimes IE doesn't recognize that FoxIt can open a PDF, and presents the dumb "Windows cannot open..." message.

NotePad2 is a must for programmers (like me) who sometimes still print out listings, especially for long or complex programs. It uses the file extension to apply the appropriate color scheme for that file type. The source code is available for the curious (or compulsive tweaker) and I've even had the program running under Linux using the wine Windows emulator.

Next is the excellent Ad-Aware which protects from spyware, much more effectively than the native Windows Defender - even after this morning's Defender definition update, Ad-Aware found five tracking cookies & allowed for their easy quarantine & subsequent removal.

My final fave is a VB6 program that I wrote to print the screen, and what do you know? It also works with Vista. Imagine that.