The Problem
Apparently Microsoft SQL Server 2005 doesn’t offer a data type that stores date only, which is all sorts of inconvenient. I am certain this was not merely an oversight, and that it was actually the result of carefully planning and design. I wish I knew what the reasoning was, though.
From MSDN:
datetime (Transact-SQL)
Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
The Solution
If I want to store only a date in the database… a date without time (despite using the datetime object)…
This particular table stores the current date for any item inserted into it, so in the CREATE TABLE block, so I include:
CREATE TABLE table_name (
created_on datetime NOT NULL DEFAULT CONVERT(datetime, CONVERT(varchar, GETDATE(), 112), 112)
);
And to make sure that data manually specifying a date gets inserted cleanly too, a trigger is helpful.
CREATE TRIGGER trig_name ON table_name
FOR INSERT AS
UPDATE table_name SET created_on = CONVERT(datetime, CONVERT(varchar, created_on, 112), 112);
How It Works
From MSDN:
CONVERT (Transact-SQL)
Converts an expression of one data type to another.
Syntax
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
So we CONVERT the datetime object into a varchar object instead, and then back again. Using the style argument “112″ to use “ISO Standard with Century”, or (yyyymmdd), which does not include time (hh:mi:ss:mmm), the time data gets lost, and defaults to 0. The format isn’t important, so long as it’s the same for both CONVERT statements (otherwise we would be saying something like “Give me the date as YYYY-MM-DD” and then “read YYYY-MM-DD as MM-DD-YYYY”, which can have some unpleasant results).
Disadvantages
Since this method still uses a datetime object, it uses 8 bytes instead of the 3 that would be used by a date object. Unless resource limits are tight, this is not a deal breaker, but it’s still inefficient.
The two CONVERT statements may impose more overhead (okay, they WILL). I haven’t measured how significant, but unless the database is INSERT-heavy, I can’t imagine it being significant.
Also, casting like this feels dirty.
References
Note:This is not an issue in Microsoft SQL Server 2008, as it supports a proper date object.
Copyright secured by Digiprove © 2011 Chris OlstromNo related posts.
