I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net

posts - 217, comments - 2280, trackbacks - 33

My Links

Advertisement

News

Hi! My name is
Mladen Prajdić I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.

SQL Server MVP

My Books

SQL Server MVP Deep Dives 2

The Red Gate Guide to SQL Server Team based Development Free e-book

Users Online:

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

<< How to Downgrade a Database from SQL Server 2005 to SQL Server 2000 | Home | SQL Server 2005 Dedicated Administrator Connection >>

How to store an incomplete date?

When doing apps that deals with date there almost always comes a question on

how to store an incomplete date.

For example:

Person A is born on 1980-02-17.

Person B is born on 1980-02 <- The person doesn't know the exact day (This is acctually a real life scenario)

How to store this wisely in db? You can't really put a datetime DateOfBirth Column.

I usually take 3 int columns (year, month, day) with constraints but i'm wondering...

Does anyone know of a better method?

kick it on DotNetKicks.com

Related Links

SQL Server Date and Time fun from all around (12/8/2009)
How to check when was SQL Server installed with a T-SQL query (7/16/2009)
Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT (12/16/2006)
Why I prefer surrogate keys instead of natural keys in database design (10/6/2009)
The Red Gate Guide to SQL Server Team based Development Free e-book (11/23/2010)

Print | posted on Saturday, February 17, 2007 2:05 AM | Filed Under [ SQL Server ]

Feedback

# re: How to store an incomplete date?

hmm....
not a bad idea Denis. Not bad at all. :))

Thanx.

2/17/2007 6:16 PM | Mladen

# re: How to store an incomplete date?

How about:

if (object_id('dbo.Dates') is not null)
begin
drop table dbo.Dates
end
go

create table dbo.Dates
(
CompleteDate datetime
,IncompleteDate varchar(6)
constraint chk_Dates_IncompleteDate
check ((1
= case len(IncompleteDate)
when 6
then isdate(IncompleteDate + '01')
when 4
then isdate(IncompleteDate + '0101')
else 0
end)
or (IncompleteDate is null))
,constraint chk_Dates_CompleteDate
check ((CompleteDate is not null and IncompleteDate is null) or (CompleteDate is null and IncompleteDate is not null))
)
go

insert dbo.Dates
(
CompleteDate
,IncompleteDate
)
select null as CompleteDate
,'198002' as IncompleteDate
union all
select null
,'1980'
union all
select '19800204'
,null
go

select *
from dbo.Dates
go

All you need now is some additional logic for data adding/modifying/retrieval and you're set. ;)

ML

2/18/2007 2:48 PM | ML

# re: How to store an incomplete date?

Thanx Matija. nice one.

I like Denis's more because it's narrower and because both columns have to be always used i can add a Non clustered index on both of them.

Or have i missed something in your solution?

2/19/2007 10:19 AM | Mladen

# re: How to store an incomplete date?

On second look, Denis's solution is better. The only thing I'd change, though, is the DateCorrectness column - I'd go with the bitmap:

0 = correct
1 = day unknown
2 = month unknown
3 = 1 + 2
4 = year unknown
5 = 1 + 4
6 = 2 + 4
7 = 1 + 2 + 4

With this modification you can test the value using the logical AND (&) operator.

ML

2/20/2007 12:15 AM | ML

# re: How to store an incomplete date?

i was aiming for a bitmap also. :)

2/20/2007 11:25 AM | Mladen

# re: How to store an incomplete date?

This has been pondered in both the GEDCOM and ISO 8601 standards.
GEDCOM is for representing genalogical data, with all the uncertainty involved (born either the 30 or 31 of january 1532, by a gregorian calendar, etc)
ISO 8601 is an international standard for the textual representation of date/time/intervals.
ISO 8601 allows you times like 2007W32: Week 32 in year 2007.

It obviously depends what sort of unknowns you need to handle:
unknown: large/small set of values for date, month, year
same for: week/weekday (some sunday during 2007?)
between date x and date y
either date x, y, z
born during orthodox easter 1634
born during a full moon early 1314 or 1313 etc etc etc.

I'd suggest storing an ISO8601 compliant string - there should be libraries to translate back and forth.

gedcom: http://homepages.rootsweb.com/~pmcbride/gedcom/55gcch2.htm#DATE_EXACT
iso 8601: http://www.cl.cam.ac.uk/~mgk25/iso-time.html

4/25/2007 10:51 AM | Arne D Halvorsen

# re: How to store an incomplete date?

thanx for this Arne. very usefull.

4/25/2007 12:10 PM | Mladen

# re: How to store an incomplete date?

i disagree, can i go register in some real org and not give my home address ? I dont remember it, because i have it written down. What about aliens ? then dont have surname :> are you sure that one day an alien wont be using the system?

Person B is born on 1980-02-01 :>

I think you are worried about wrong problems, its just like a elevator desinger was worried about how blind and without hands people will use it :>

5/5/2007 6:11 AM | raveman

# re: How to store an incomplete date?

so to which problem are you acctually reffering to, raveman?

because we acctually have some people that don't know their exact birthdate.

5/5/2007 2:24 PM | Mladen

Comments have been closed on this topic.

Powered by:

Copyright © Mladen Prajdić