Convert input explicitly at your client; don't rely on the database to "figure it out"
24 Jul 2008 1:12pm GMT A common mistake beginners make when working with SQL is trying to format their output at the database layer, as opposed to simply doing this at the presentation layer (i.e., client application, reporting tool, web page, etc). I've covered that quite a bit in various blog posts, but I've only touched upon another similar issue which I feel is equally as important and also commonly mishandled. In the SqlTeam forums, I often see code that accepts DateTime input in the form of a string value...
Date Only and Time Only data types in SQL Server 2005 (without the CLR)
31 Oct 2007 1:13pm GMT In this post, I showed a simple way to create simple but useful Date and Time user-defined data types in SQL Server 2000. Here's how to do it in SQL Server 2005, without the need for CLR types or anything fancy. First, we must create two user defined data types: create type Date from dateTime create type Time from dateTime So, internally (and externally to our clients), these types are really just DateTime. But, we will apply some rules to these types so that the Date data type...
Working with Time Spans and Durations in SQL Server
15 Oct 2007 1:28pm GMT If you read this blog on a regular basis, you probably know that a frequently covered topic is dates and times and the use of the DateTime data type. Well, along those same lines, my latest article has just been published over at SQL Team: Working with Time Spans and Durations in SQL Server. From the Article: What is the best way to return the "duration" of an event in SQL, given the start and end datetime values? How can we add up these durations to return grand totals? What data...
Filter by month (plus other time periods)
14 Sep 2007 4:21pm GMT Introduction Previously, I wrote about grouping transactions by month. Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month. There are two parts to this equation: First, what is the best way to declare parameters that will be used to indicate which month you are looking for? Second, how can we efficiently and easily make use of those parameters to get back the data we need? Let's take a look at some...
Group by Month (and other time periods)
10 Sep 2007 3:28pm GMT When you need to summarize transactional data by Month, there are several ways to do it, some better than others. What to ultimately choose depends on your needs, but remember: Keep it short and simple in T-SQL, and always do all of your formatting at your presentation layer where it belongs. read more...