Creating a Date - or Time - Only Column in SQL Server

I understand that in SQL Svr 2000, all date/time fields store both the date
and the time. Is there a way through a constraint or trigger to force a table
to store only the date portion or time portion of the entry? For example, a
"DateHeld" field would actually contain only 2/14/2007, rather than
"2/14/2007 12:00:00 AM".
Or is there a better solution? I would rather not have to keep writing
functions to convert these combined date/time values when I want to use them
as just a date or just a time. Thanks! George
> and the time. Is there a way through a constraint or trigger to force a
> table
> to store only the date portion or time portion of the entry?
USE tempdb;
GO
CREATE TABLE dbo.foo
(
dt SMALLDATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, dt)) = dt),
tm DATETIME CHECK (DATEADD(DAY, 0, DATEDIFF(DAY, 0, tm)) = '19000101')
);
SET NOCOUNT ON;
INSERT dbo.foo(dt, tm) SELECT '20070101', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101 19:34', '19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '20070101', '19000102 19:34';
GO
-- fails:
INSERT dbo.foo(dt, tm) SELECT '19:34', '20060505';
GO
SELECT * FROM dbo.foo;
GO
DROP TABLE dbo.foo;
GO

> Or is there a better solution? I would rather not have to keep writing
> functions to convert these combined date/time values when I want to use
> them
> as just a date or just a time.
Why don't you let the presentation side of things handle the formatting and
display of the date only or time only value?
|||"Aaron Bertrand [SQL Server MVP]" wrote:

> Why don't you let the presentation side of things handle the formatting and
> display of the date only or time only value?
>
Well, doing it at the interface end means a lot of repetitious formatting in
different places. I'd rather fix it at the source one time. I just find it
kind of amazing that SQL Server does not support current_date or
current_time, for example.
I presume the original T-SQL stuff at the top is used to build a trigger.
Thanks for the help, Aaron.