2

I am using SQL Server 2005 Express Edition. I want to store date of birth in a table, which datatype should I use for that?

Datetime provides time also with the date.

Is there any way in SQL Server 2005 to store only date?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
vatspoo
  • 391
  • 3
  • 8
  • 14
  • 1
    Not really - SQL Server **2008** introduced the `DATE` (date-only) datatype. SQL Server 2008 Express would be free, too..... – marc_s Jul 14 '11 at 11:44
  • @marc_s yeah m aware of that but want to stay with SqlServer2005 for the time being – vatspoo Jul 14 '11 at 11:50

1 Answers1

3

Use smalldatetime (4 bytes storage) and add a check constraint. Do not use char or such.

The CHECK CONSTRAINT would be this, based on this question Best approach to remove time part of datetime in SQL Server

DOBCol = DATEADD(day, DATEDIFF(day, 0, DOBCol), 0)
Community
  • 1
  • 1
gbn
  • 422,506
  • 82
  • 585
  • 676
  • ...but how will that remove the time part? Can you please explain? – vatspoo Jul 14 '11 at 11:56
  • @vatspoo: this ensures that time won't be stored. datetime only has time if you provide,. Will your client code send time for DOB? – gbn Jul 14 '11 at 11:57
  • No i just have to store date only but i am not able to comprehend how DATEADD(day, DATEDIFF(day, 0, DOBCol), 0) will remove the time part. Could you please elaborate it? – vatspoo Jul 15 '11 at 05:54
  • It works on zero being 01 jan 1900. It works out number of whole days since then, and then adds back thus removing the time. As per my link, it is the *most efficient* way to remove time – gbn Jul 15 '11 at 10:01
  • ok... how should I use it?like this is how i have created the table create table biodata (Name varchar(20), Gender varchar (1), DoB Datetime, FatherName varchar (20), Education varchar(10), Address varchar(35), Pincode varchar(6), DoJ datetime) And this is how i am inserting the values into the table... insert into biodata values ('Sally', 'F', 1985-11-5, 'Gissele Mary', 'BBA', '19/26 MS colony, Goa','110034', '2011-07-01') Please guide me through this.. – vatspoo Jul 18 '11 at 10:50