tags:

views:

50

answers:

2
+2  Q: 

Why doesn't SQL Server have a pure date and pure time data type?

I have a few SQL Server tables with a datetime field that really only has to contain dates... and the fact that it's a datetime object violates data integrity, because it's possible for data to be inserted with a time - which isn't really valid for this field. The result is that we have to convert it and strip off the time every single time we use it. We've considered adding triggers to make sure that no data can even get in with a time attached... but it seems to me that this should really be part of the database software, and shouldn't need to be specifically programmed each time.

Why doesn't SQL Server have separate date and time data types? Is this going to change in the near future? Do other database platforms have this functionality?

+3  A: 

SQL Server 2008 has DATE and TIME data types.

What version are you using?

Cade Roux  2010-06-10 14:33:29
(wow. quick answer.) I think we're still on 2005... was it just added for 2008?
froadie  2010-06-10 14:34:46
Yes, this was added in 2008
ZippyV  2010-06-10 14:35:55
so... what's the accepted implementation of this if you're still on an older version?
froadie  2010-06-10 14:37:08
... upgrade? :)
GalacticCowboy  2010-06-10 14:43:10
@GalacticCowboy... I wish :P
froadie  2010-06-10 14:45:06
The workaround for 2005 and earlier for date is just ignore the time portion and trim when needed. For time, use a standard date portion like 1/1/1900.
Joel Coehoorn  2010-06-10 14:58:27
@froadie Upgrading to new data types even if they were available would still be significant work - if you can change your schema in 2005, add a persisted computed column which removes the time portion and do not use the underlying datetime anywhere. If you have a view layer or stored procedure layer, this is a fairly simple refactoring to stop exposing the underlying datetime to anyone, and you may not even need to change your client application above that layer.
Cade Roux  2010-06-10 15:15:16
+1  A: 

SQL Server has a pure date and time data type since version 2008, they also added a bunch of new datetime types, now it also goes all the way back to year 1 instead of 1753

SQLMenace  2010-06-10 14:34:38

related questions

Handling and storing elapsed time
How do I convert local time to UTC in Python?
Mono's DateTime Serialization
Best practices for DateTime serialization in .Net framework 3.5/SQL Server 2008
Parsing exact dates in C# shouldn't force you to create an IFormatProvider
C# Casting vs. Parse
.NET Date Const (with Globalization)
Dynamic regex for date time formats
What is the simplest way to find the difference between 2 times in python?
Best way to convert DateTime to "n Hours Ago" in SQL
What's a good way to overwrite DateTime.Now during testing?
Datetime arithmetic with a string in Ruby
How can I get the DateTime for the Start of the Week?
Is DateTime.Now the best way to measure a function's performance?
SQL Server DateTime conversion failure
Natural language date/time parser for .NET?
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Why does this python date/time conversion seem wrong?
Dealing with PHP server and MySQL server in different time zones
How do I parse and convert DateTime's to the RFC 3339 date-time format?
HOWTO - Compare a date string to datetime in SQL Server?
Best method for varchar date validation in Sybase (T-SQL)?
What's the BEST way to remove the time portion of a datetime value (SQL Server)
How do I calculate relative time?
How do I calculate someone's age in C#?