example when I create a table how when I define datetime as datatype is there a way to define it so only date shows and never time? i need it for a birthday field.

CREATE TABLE Customer
(
Cust_ID int Identity (1000,1) primary key NOT NULL,
Cust_Birthday datetime NULL,
);

SQL Server? Which version?

pootle flump ur codings are working excelent.

there are several comingled issues -- retrieving only the date portion of a DATETIME column when it contains datetime values that include a time portion other than 00:00:00 (that's one), ensuring that only datetime values are entered into the column, so that nothing actually need to be done other than retrieve the value (that's two), and retrieving a value from a DATETIME column and disregarding the time portion when displaying it in the app, whether the time portion is 00:00:00 or not (that's three)...

in my opinion you should not store times for birthdates so there's really no problem that needs solving here

the real WTF here is that i talked myself out of giving the dateadd/datediff solution i normally offer

over to you, poots

To answer "is there a way to define it so only date shows", generally, no. Data types say what data can go in there, not how it looks.

You can, however, force the time to be something reliable like 00:00:00 by adding a check constraint that HOUR(Cust_Birthday) = 0 AND MINUTE(Cust_Birthday) = 0 AND SECOND(Cust_Birthday) = 0. That will simplify your date arithmetic because you will be guaranteed that any difference between two dates is an integral number of days.

Further, you can create a query that only retrieves the date and that formats it pretty well however you like. Rudy's web site has a few examples of e.g. calculating ages.