Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2008
    Posts
    24

    How do I remove time for date

    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,
    );

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    SQL Server? Which version?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by Datanalysis
    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,
    );
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •