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.

Blogger Support is Super

If you've noticed that this blog was MIA for the last few days, I had been fooling around with a 3rd blog, then decided to discard it -but- I accidentally deleted THIS one. Oops. And I had no backups, shame on me.

Blogger Support to the rescue - "Karl" was able to restore all my posts going back to Jan 2005. All the more impressive, considering that this is a free service provided by Google.

Thanks to Karl and all of the Blogger Support team.

Welcome

Welcome to the Mitchell Data Base blog. On these pages, I hope to cover topics of interest to database consultants and users. The primary database platforms include: Microsoft Access and Microsoft SQL Server, but older platforms such as dBase may also be covered from time to time. Any and all comments are welcome. If you have sample files or other materials to contribute, please send an email to webmail@wvmitchell.com and you will be contacted privately with posting information. Thank you for your interest.