Well, yes but you can store the datetime value with zero as the integer (date) portion. It's equivalent to storing it as a particular time on January 1st, 1900 (or whatever...). I've seen a lot of applications do this, but as Pat pointed out it is usually unnecessary.
Since databases are about storing information, why do you want to discard the date portion of your time value?
If it's not practically useful, then it's practically useless.
Alot of our data is "date" only type information. Having just the date only makes queries and coding easier etc... I was toying with the idea of just storing time separately in the few instances that I need it. But I see now that I would have to store it as a different data type or as you stated ignore the default date of 1/1/1900.
Yeah I know. The time component can be textual for most cases. It is usually for display only(if I need to search the time component I will save it in a datetime field and ignore date part in the rare cases I care about time).
There is a lot of old code (Access and VB) here that saves the date only (Access auto-magically does this for us through bound forms) that the end users use. If a time component is in the data it will confuse users (no extra help in that department req'd.).
Until I get (full) control of the old apps here and get my database standardized I need to maintain the original format.
I have written several SP's and DTS packages that replace old Access front end functions (my slow but sure moving of business rules into the backend) so therefore my date formatting issues have cropped up. The Access queries do not work right with the Time component in most cases. The SQL code puts the default time in. So rather than try to fix old apps to work with the issue (which are already slated for rewrites) easier to just coerce the data for now.
Someday it will all be good! Job security right?
Sorry for the extra verbage...caught me on a chatty day. Decaf tomorrow!
While it is a bit of a pain in the patoot, you can add a trigger to forceably rip the time off (which cleans it up with no code changes required), or a constraint that will never allow the time in the first place (which forces code to get the value right in the first place).
Yeah Brett, but this incarnation of the Yak Corral is WAY down from the original. We've got a LONG way to go to get anywhere close to the levels that inspired!
CREATE FUNCTION udf_Time(@x varchar(25))
DECLARE @y char(8)
SELECT @y = CONVERT(char(8),CONVERT(datetime,@x),108)
WHERE ISDATE(@x) = 1
DROP FUNCTION udf_Time