What data type should I use for "date" of birth

  • 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

  • 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/

  • 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

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply