January 13, 2012 at 8:24 pm
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
January 13, 2012 at 10:20 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 13, 2012 at 10:37 pm
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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
January 14, 2012 at 8:15 am
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
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply