Reader Challenge - 20 Dec 1999

Test your SQL Server savvy

[Editor's Note: Subscribe to the SQL Server Magazine UPDATE newsletter (http://www .sqlmag.com/resources/main/cfm) to solve next month's SQL Server problem. First-place winners receive $100; second-place winners receive $50.]

AND THE WINNERS ARE . . .

Congratulations to Umachandar Jayachandran of Optimal Solutions, West Palm Beach, Florida, and Ping Long of Learn2.com, Pryor, Oklahoma. Umachandar won first prize of $100 for the best solution to the Reader Challenge. Ping won second prize of $50.

Problem

The SQL Server datetime data type contains both date and time portions. In Rick's Invoice table, each invoice is time-stamped to show when a user placed the invoice (column name is inv_dt). The SQL Server GETDATE() function generates this timestamp. Because this column contains both date and time portions, separating the date from the time can be tricky. For example, sometimes Rick needs to group only by date, and sometimes he needs to perform joins on date. The same situation occurs for the time portion.

Help Rick design a table structure that developers can use to easily address invoice by date alone and by time alone. Your solution needs to expose the information as datetime data, not character or integer data. Rick's looking for a solution that will avoid extra storage usage. Include only the relevant columns in your solution.

Solution

Rick can take advantage of the fact that SQL Server sets the time portion of the datetime to a standard value if you express only the date portion (and vice versa). In SQL Server 7.0, you can define computed columns, which generate values based on a computation. This function lets Rick extract the date only (as a string, using the CONVERT function and formatting codes) from the inv_dt column and base the date column on that string. He can do the same for invoice time. Here's a plausible, simplified table structure for the invoice table:

CREATE TABLE Invoice(inv_id INT IDENTITY PRIMARY KEY,inv_dt DATETIME DEFAULT GETDATE(),inv_date AS CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112)),inv_time AS CONVERT(DATETIME, CONVERT(CHAR(5), GETDATE(), 114)) )

Rick chooses the formatting code 112 because it returns a date part, formatted as an unseparated datepart string (yyyymmdd). This formatting makes the date part independent of language and SET DATEFORMAT settings. Rick can also create a view that exposes these calculated columns. A drawback of both alternatives is that you can't create an index on the calculated columns. To do that, you need to physically store the date and time values.