You could have the same time for all rows in the column where you only want the date, and vice
versa. Most often, you then opt for standard date 1 jan 1900 and standard time 00:00:00.
You can have a default for the column where you "only want the date" as:
DEFAULT CONVERT(char(8), CURRENT_TIMESTAMP, 112)
And where you "only want the date":
DEFAULT CONVERT(char(8), CURRENT_TIMESTAMP, 114)
You most probably want CHECK constraints as well. Just extract the date portion (where you only
want time) and make sure it is '19000101', using technique above. And similar for time portion.
As for reading data. As there are no date-only and vice versa, you need to use CONVERT and make
the datetime data strings. Use CONVERT for that, see examples about for starter...
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sql...
Quote:> I have an application which needs to store date and time fields
> separately, but there don't seem to be any basic date and time types
> in SQL Server, just datetimes.
> I am currently using datetime for both the date fields and the time
> fields - what would be the best way of ensuring that only the date
> portion of date fields and the time portion of time fields are
> extracted from the database? Is there a way I can do this as a rule
> for those fields, or alternatively what would be the SQL code to get
> just the date and time portions?
> Dan Sumption
by