Date of Birth - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date of Birth

Author

Topic

perem
Starting Member

4 Posts

Posted - 2010-03-16 : 10:33:46

I'm designing a database with records of people. I'm trying to record a persons date of birth but it always adds a time along with the data of birth. Is there any way I can just add the DOB?
This is the code I'm using

create table cust
(custID int NOT NULL identity (1,1) PRIMARY KEY,
Name varchar(30),
Address varchar(30),
DOB date)

Any help would be great,
thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-03-16 : 10:48:04

you are using date data type. It will not contain the time. Unless you are using datetime data type

KH
[spoiler]Time is always against us[/spoiler]

perem
Starting Member

4 Posts

Posted - 2010-03-16 : 10:51:28

I started using datetime but show the date and time together.
Now have changed it to just date but it says datatype not recognised?

Ehan
Starting Member

19 Posts

Posted - 2010-03-16 : 10:56:01

you will have to covert into string.

DECLARE @DOB DATETIME
SET @DOB = '2010-12-31'
SELECT CONVERT(VARCHAR(10),@DOB,101)

notice what happens when you cast it back to datetime

SELECT CAST(CONVERT(VARCHAR(10),@DOB,101) AS DATETIME)

perem
Starting Member

4 Posts

Posted - 2010-03-16 : 11:09:42

can you post the code up using the table we have created and how it would work together with the declare ? thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:17:17

quote:
Originally posted by perem

I started using datetime but show the date and time together.
Now have changed it to just date but it says datatype not recognised?

date datatype is available only from sql 2008 onwards

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:18:29

quote:
Originally posted by Ehan

you will have to covert into string.

DECLARE @DOB DATETIME
SET @DOB = '2010-12-31'
SELECT CONVERT(VARCHAR(10),@DOB,101)

notice what happens when you cast it back to datetime

SELECT CAST(CONVERT(VARCHAR(10),@DOB,101) AS DATETIME)

no need of doing costly type conversion just to strip off time. for that just use below

http://visakhm.blogspot.com/2010/01/some-quick-tips-for-date-formating.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:19:59

quote:
Originally posted by perem

can you post the code up using the table we have created and how it would work together with the declare ? thanks

Just strip of time part from date value passed while storing in dob field and then it will have time part as 00:00:00. then you can use formatting functions at front end to show date alone
Refer last posted link for more details

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

perem
Starting Member

4 Posts

Posted - 2010-03-16 : 11:29:06

Would it work the same way using SQL server 2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-16 : 11:31:22

yup. why not?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources