Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

What data type should I use for "date" of birth Expand / Collapse
Author
Message
Posted Friday, January 13, 2012 8:24 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 13, 2012 3:24 PM
Points: 7, Visits: 34
I want to store "date" of birth.

Normally a "date" of birth consist of year, month, day, but there obviously will occur that I don't know the year or that I only know the year or ...

So this would be valid dates of birth

1929
1929-03-25
03-25

25/03
25/3/1929

don't know yet if I should use ISO or US dates...

So what data type should I use for storing?

Is it possible to use Date?

// Anders
Post #1236070
Posted Friday, January 13, 2012 10:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
I would avoid anything other than date or datetime for storing dates. Storing this type of data in any other datatypes will cause nothing but headaches down the road. It does sound like you have some business rules to iron out when years are unknown but I bet you could up with a reasonable solution. Maybe if you don't know the year then you don't really know the birthdate so it is null. Or maybe you use a standard year like 1800 when you don't know the year.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1236086
Posted Friday, January 13, 2012 10:37 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:20 PM
Points: 17,600, Visits: 15,462
I'm with Sean - use a Date datatype.

Sit down and chat with the business about the cases why any part of the date of birth would be unknown.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1236089
Posted Saturday, January 14, 2012 8:15 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 12:48 PM
Points: 9,923, Visits: 11,169
I was curious to see how difficult it would be to implement a table and indexed view combination that would allow you to do all the things you require. In the following demo, there is a table with separate columns for DOB day, month, and year; and a computed DOB (as date) column where all the components exist. There is an indexed view that only shows complete DOBs. The indexed view allows INSERT, UPDATE, and DELETE of complete records...

USE tempdb
GO
CREATE TABLE dbo.DateOfBirth
(
person_id integer NOT NULL,
dob_day tinyint NULL CHECK (dob_day BETWEEN 1 AND 31),
dob_month tinyint NULL CHECK (dob_month BETWEEN 1 AND 12),
dob_year smallint NULL CHECK (dob_year BETWEEN 1850 AND 2099),
dob AS
-- Computed column for full date of birth
CASE
WHEN
dob_day IS NOT NULL
AND dob_month IS NOT NULL
AND dob_year IS NOT NULL
THEN
CONVERT(
date,
CONVERT(
char(8),
dob_year * 10000 +
dob_month * 100 +
dob_day),
112)
ELSE NULL
END,

CONSTRAINT [PK dbo.DateOfBirth person_id]
PRIMARY KEY CLUSTERED (person_id),

CONSTRAINT [CK dbo.DateOfBirth valid date]
CHECK
(
1 = CASE
WHEN
-- Not all day, month, year can be NULL (delete if not applicable)
COALESCE(dob_day, dob_month, dob_year) IS NULL THEN 0
WHEN
-- Any other combination of NULLs is ok
(dob_day + dob_month + dob_year) IS NULL THEN 1
WHEN
-- No NULLs, so must not fail the ISDATE test
ISDATE(CONVERT(char(8), dob_year * 10000 + dob_month * 100 + dob_day)) = 0 THEN 0
WHEN
-- No NULLs, passes ISDATE test, now check date is not in the future
CONVERT(date, CONVERT(char(8), dob_year * 10000 + dob_month * 100 + dob_day)) <= CONVERT(date, GETDATE()) THEN 1
ELSE
-- Failed the future date test
0
END
)
)
GO
-- Just to show an index can be created on the computed column
-- (sadly it cannot be a filtered index)
CREATE NONCLUSTERED INDEX uq1
ON dbo.DateOfBirth (dob)
GO
-- Sample data
INSERT dbo.DateOfBirth
(person_id, dob_day, dob_month, dob_year)
VALUES
(1, 11, 7, 1947), -- 11 July 1947
(2, 25, 3, NULL), -- 25 March
(3, NULL, NULL, 1929), -- Year 1929
(4, NULL, 5, 1985), -- May 1985
(5, 29, 2, 2000), -- 29 February 2000
(6, 10, 8, 2011) -- 10 August 2011
GO
-- Show the contents of the table
SELECT
e.person_id,
e.dob_day,
e.dob_month,
e.dob_year,
e.dob
FROM dbo.DateOfBirth AS e;
GO
-- Can create an indexed view to simulate a filtered index
CREATE VIEW dbo.DateOfBirthComplete
WITH SCHEMABINDING
AS
SELECT
person_id,
dob
FROM dbo.DateOfBirth AS e
WHERE
-- No incomplete dobs
e.dob IS NOT NULL;
GO
-- Index the view
CREATE UNIQUE CLUSTERED INDEX
[CUQ dbo.DateOfBirthComplete person_id]
ON dbo.DateOfBirthComplete
(person_id)
GO
-- No NULL dates of birth, and no day, month, year columns
SELECT
dob.*
FROM dbo.DateOfBirthComplete AS dob WITH (NOEXPAND)
GO
-- Can UPDATE person_id through the view
-- Cannot INSERT or UPDATE the computed column (yet)
UPDATE dbo.DateOfBirthComplete
SET person_id = 7
WHERE person_id = 1
GO
-- Can DELETE through the view
DELETE dbo.DateOfBirthComplete
WHERE person_id = 6;
GO
-- Enable INSERTs through the view
CREATE TRIGGER dbo.[DateOfBirthComplete IOI]
ON dbo.DateOfBirthComplete
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON;
SET ROWCOUNT 0;

INSERT dbo.DateOfBirth
(person_id, dob_day, dob_month, dob_year)
SELECT
i.person_id,
DAY(i.dob),
MONTH(i.dob),
YEAR(i.dob)
FROM inserted AS i
END
GO
-- Insert through the view is now possible
INSERT dbo.DateOfBirthComplete
(person_id, dob)
VALUES
(1, '1980-11-26'),
(6, '1990-04-13'),
(8, '2000-12-25')
GO
-- Enable UPDATEs through the view
CREATE TRIGGER dbo.[DateOfBirthComplete IOU]
ON dbo.DateOfBirthComplete
INSTEAD OF UPDATE
AS
BEGIN
SET NOCOUNT ON;
SET ROWCOUNT 0;

-- Updating the PRIMARY KEY?
IF UPDATE(person_id)
BEGIN
-- Replace UPDATE with DELETE then INSERT
DELETE dbo.DateOfBirth
FROM deleted AS d
WHERE
d.person_id = dbo.DateOfBirth.person_id

INSERT dbo.DateOfBirth
(person_id, dob_day, dob_month, dob_year)
SELECT
i.person_id,
DAY(i.dob),
MONTH(i.dob),
YEAR(i.dob)
FROM inserted AS i
END
ELSE
BEGIN
-- Not changing the PK, update in place
UPDATE dbo.DateOfBirth
SET
person_id = i.person_id,
dob_day = DAY(i.dob),
dob_month = MONTH(i.dob),
dob_year = YEAR(i.dob)
FROM inserted AS i
JOIN dbo.DateOfBirth AS e ON
e.person_id = i.person_id
END
END
GO
-- Modify computed column through the view
UPDATE dbo.DateOfBirthComplete
SET dob = '2000-12-26'
WHERE person_id = 8

-- Show final table and view contents (all in sync)
SELECT *
FROM dbo.DateOfBirth AS dob

SELECT *
FROM dbo.DateOfBirthComplete AS dob WITH (NOEXPAND)
GO

-- Tidy up
DROP VIEW dbo.DateOfBirthComplete
DROP TABLE dbo.DateOfBirth





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1236145
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse