Jeff Smith Blog

Convert input explicitly at your client; don't rely on the database to "figure it out"

Thu Jul 24, 2008 by Jeff Smith in t-sql, net-c-vb, techniques, datetime-data

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). Read more →

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

Wed Oct 31, 2007 by Jeff Smith in techniques, sql-server-2005, datetime-data

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. Read more →

Working with Time Spans and Durations in SQL Server

Mon Oct 15, 2007 by Jeff Smith in t-sql, links, datetime-data

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. Read more →

Filter by month (plus other time periods)

Fri Sep 14, 2007 by Jeff Smith in techniques, efficiency, report-writing, datetime-data

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. Read more →

Group by Month (and other time periods)

Mon Sep 10, 2007 by Jeff Smith in t-sql, group-by, datetime-data

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. Techniques to Avoid Read more →

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

Thu Aug 30, 2007 by Jeff Smith in links, sql-server-2008, datetime-data

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. Read more →

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

Wed Aug 29, 2007 by Jeff Smith in t-sql, efficiency, database-design, datetime-data

The Importance of Data Types Imagine that SQL Server only provided two data types: the MONEY data type to store numeric values, and VARCHAR to store text. If you are designing a database in this scenario and you need to store or return integer values, which data type – MONEY or VARCHAR – would you use? Read more →

Data Types – The Easiest Part of Database Design

Tue Jul 3, 2007 by Jeff Smith in miscellaneous, database-design, datetime-data

Database design can be very complicated, and it truly is an art as opposed to a science; sometimes there are multiple correct ways to model the same data with pros and cons to each. Read more →

SQL Data Modeling: Entities versus Attributes

Tue Jun 19, 2007 by Jeff Smith in techniques, database-design, datetime-data

Introduction <o:p>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? Read more →

SQL Challenge Response: Finding Consecutive Available Blocks in a Schedule

Tue May 15, 2007 by Jeff Smith in t-sql, techniques, efficiency, links, datetime-data

(Note: Updated 5/15/2007 @ 12:25 PM EST to show another possible solution with different results.) Here's my response to the SQL Challenge given here. The challenge involves having a schedule table with days and times, and displaying all available consecutive free time slots to schedule an event. Read more →

How to format a Date or DateTime in SQL Server

Fri Apr 13, 2007 by Jeff Smith in t-sql, techniques, efficiency, database-design, datetime-data

Question: How do you create a DateTime in a specific format in SQL Server? Answer: You don't. You can't. The only way to translate a DateTime into a specific format is to convert it to a VARCHAR or other "string" data type. Read more →

Essential SQL Server Date, Time and DateTime Functions

Tue Jan 2, 2007 by Jeff Smith in t-sql, code-library-sql, datetime-data

Part I: Standard Date and Time 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. Read more →

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

Thu Dec 2, 2004 by Jeff Smith in t-sql, code-library-sql, datetime-data

In this post: http://weblogs.sqlteam.com/jeffs/archive/2004/12/02/2954.aspx 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. Read more →