Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 155, comments - 2679, trackbacks - 64

My Links



Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.



Post Categories




DateTime Data

Covering the DateTime data type, and working with Dates and Times in general using T-SQL. As always, remember: Use the right data type, and let your front end do all the formatting.
Convert input explicitly at your client; don't rely on the database to "figure it out"

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 (say, from a TextBox on a web form) and uploads that value to the database written...

posted @ Thursday, July 24, 2008 9:12 AM | Feedback (11) | Filed Under [ T-SQL .NET (C# / VB) Techniques DateTime Data ]

Date Only and Time Only data types in SQL Server 2005 (without the CLR)

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 will always be constrained to a time exactly at midnight, and the the Time data type...

posted @ Wednesday, October 31, 2007 9:13 AM | Feedback (16) | Filed Under [ Techniques SQL Server 2005 DateTime Data ]

Working with Time Spans and Durations in SQL Server

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 types should be used to return this data to our clients?...

posted @ Monday, October 15, 2007 9:28 AM | Feedback (0) | Filed Under [ T-SQL Links DateTime Data ]

Filter by month (plus other time periods)

Another common area of difficulty or confusion for SQL beginners is how to efficiently retrieve data just for a single month. What is the best way to declare parameters that will be used to indicate which month you are looking for, and how can we efficiently and easily make use of those parameters to get back the data we need? read more...

posted @ Friday, September 14, 2007 12:21 PM | Feedback (5) | Filed Under [ Techniques Efficiency Report Writing DateTime Data ]

Group by Month (and other time periods)

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...

posted @ Monday, September 10, 2007 11:28 AM | Feedback (58) | Filed Under [ T-SQL GROUP BY DateTime Data ]

SQL Server 2008 - Enhancements in Date and Time Data Types (link)

Speaking of dates and times, there's a nice post from Ravi. S. Maniam over at the msdn blogs regarding the new and exciting Enhancements in Date and Time Data Types for SQL Server 2008. It will be very interesting to see how date and time usage changes once SQL Server 2008 becomes the most commonly used edition.  Of course, since even SQL 2005 still doesn't seem to be as widely adopted as I would like, who knows when that will be!

posted @ Thursday, August 30, 2007 10:05 PM | Feedback (1) | Filed Under [ Links SQL Server 2008 DateTime Data ]

Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME

The next time you are working with dates and times, please remember: how would you handle things if you were working with integers and decimals? The same logic and reasoning applies. Be smart, let SQL do the work for you and use the right data types for the job, even if things don't always "look" right. read more...

posted @ Wednesday, August 29, 2007 10:04 AM | Feedback (33) | Filed Under [ T-SQL Efficiency Database Design DateTime Data ]

Data Types -- The Easiest Part of Database Design

I see it time and time again in forums -- "dates" that don't sort properly, "numbers" that don't add correctly, "boolean" data with 10 different values, and so on ... Since we are rarely provided any DDL to review, it often takes many posts going back and forth until we finally realize: "wait ... you aren't using a datetime data type to store these dates?!" read more...

posted @ Tuesday, July 03, 2007 10:13 AM | Feedback (9) | Filed Under [ Miscellaneous Database Design DateTime Data ]

SQL Data Modeling: Entities versus Attributes

There’s a handy little rule of thumb I use when developing my database models. It comes up often in situations like this: If we are storing phone numbers in the database, should AreaCode be a “free-form” char(3) column? Or should it be a foreign key reference to an AreaCodes table? read more...

posted @ Tuesday, June 19, 2007 11:47 AM | Feedback (3) | Filed Under [ Techniques Database Design DateTime Data ]

SQL Challenge Response: Finding Consecutive Available Blocks in a Schedule

Here's my response to a SQL Challenge, regarding how to find consecutive free time slots in a schedule. This can sometimes be tricky to solve in SQL, but using either of the two techniques shown here, it is actually pretty easy. (Updated to show 2 possible solutions)


posted @ Tuesday, May 15, 2007 9:52 AM | Feedback (6) | Filed Under [ T-SQL Techniques Efficiency Links DateTime Data ]

How to format a Date or DateTime in SQL Server

Everything you ever wanted to know about how to use SQL Server's advanced features to format Dates into any format that you need! Here, for the first time, all in one place, is the secret that no one wants you know about how easy it is to format data in T-SQL!


posted @ Friday, April 13, 2007 4:45 PM | Feedback (56) | Filed Under [ T-SQL Techniques Efficiency Database Design DateTime Data ]

Essential SQL Server Date, Time and DateTime Functions

I've posted some variations of these before, but here they all are in 1 easy package: The essential date and time functions that every SQL Server database should have to ensure that you can easily manipulate dates and times without the need for any formatting considerations at all.


posted @ Tuesday, January 02, 2007 11:38 AM | Feedback (98) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Date Only and Time Only User Defined Dataypes in SQL Server 2000

I talked about separating dates from times for certain columns in your database. One thing I have never really used before in SQL Server 2000 is user-defined datatypes and rules. Rules are like CHECK constraints, but from what I understand they are very non-standard.


posted @ Thursday, December 02, 2004 2:41 PM | Feedback (8) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Breaking apart the DateTime datatype -- Separating Dates from Times in your Tables

I don't like storing dates along with the time portion in my database tables. Sure, I use the datatype, but I wish SQL provided a “Date” datatype and a “Time” datatype that were separate. It really sometimes requires too much work in T-SQL to separate the date portion from the time portion, and often in a WHERE clasue you don't care about the time, you just want data for a single day.

posted @ Thursday, December 02, 2004 11:17 AM | Feedback (10) | Filed Under [ T-SQL DateTime Data ]

Create a Date in T-SQL

Another way to create dates easily, w/o using CONVERT or using a UDF (i.e., if you have SQL 7.0), if you are starting out with integers representing the year, month and day of the date you need to create:


posted @ Monday, December 15, 2003 10:07 AM | Feedback (6) | Filed Under [ T-SQL Code Library - SQL DateTime Data ]

Creating Dates and Date Math in T-SQL with a UDF

To me, this is a pretty handy function to have in your toolbox ...

posted @ Tuesday, December 09, 2003 10:50 AM | Feedback (27) | Filed Under [ T-SQL DateTime Data ]

Powered by:
Powered By Subtext Powered By ASP.NET