Coop’s Blog

Coopers personal blog site

Cool week coming up at Knetwit

Looking forward to a fun week at Knetwit.  We have the release of Gizzard (code name for a new application).  And we have an awesome new feature to release for Knetwit.

February 9, 2009 Posted by | Uncategorized | 1 Comment

Common SQL Mistake

DateTime datatypes within WHERE clauses frequently give people the wrong results.  I see this often.  I am having to fix a lot of reports now to correct this issue.  Here is the offending statement:  “WHERE CreatedDate between @BeginDate and @EndDate”.  By default, a datetime value will give the time portion of “00:00:00″; the full value looks like this – “1/30/2009 00:00:00″.

See the problem with this WHERE statement – “WHERE CreatedDate BETWEEN ’1/1/2009 00:00:00′ AND ’1/30/2009 00:00:00′”?  The problem is that you will not get data for the date of January 30th since that value is only up to midnight on the 30th.

Here is two ways of fixing it.  Both of these methods depend on the programmer modifying the @EndDate parameter.

  1. WHERE CreatedDate BETWEEN ’1/1/2009 00:00:00′ AND ’1/30/2009 23:59:59′.  This takes you to the end of the day on the 30th.
  2. WHERE CreatedDate BETWEEN ’1/1/2009 00:00:00′ AND ’1/31/2009 00:00:00′  This takes you to the start of the next day.

An easy fix for this (examples are using Transact SQL) is the following:  “WHERE CreatedDate BETWEEN @BeginDate and ADDDATE(day,1,@EndDate)”  This turns the EndDate parameter into the next day at midnight; it is like example 2 above.

February 3, 2009 Posted by | Uncategorized | Leave a comment

Knetwit New Release

Excited about new software release on Monday. We are releasing our new Knetwit Store with 1,000′s of items to choose from. Plus there will be a new “Books” page and book searching. Go to Knetwit to see for yourself.

February 1, 2009 Posted by | Uncategorized | , | 2 Comments

   

Follow

Get every new post delivered to your Inbox.