I have a situtation where I need to record dates of reports.  Usually the dates on these documents include the standard month, day, and year (such
as February 15, 2008).  However, sometimes they are published as just "February 2008".  I considered storing the "February 2008" date as February
1, 2008, but this is an inaccurate record of the published date.

I've considered storing the date as three separate numeric fields:  "month", "day", and "year" and just having day be null (or zero) when it is
unknown.  For searching, I figured that I could have a computed field that would build the date in the form "YYYYMMDD" that would allow me to still
sort sequentially (if DD [day]) is null it we be represented as zero (such as 20080200) or if it is known it would be the actual value, of course
(20080215).

This also presents some minor user interface issues since I won't be able to use standard date entry components.

Is my approach the best way to handle this situation?  Any suggestions would be appreciated.

Greg

Greg,

<< I have a situtation where I need to record dates of reports.  Usually the
dates on these documents include the standard month, day, and year (such as
February 15, 2008).  However, sometimes they are published as just "February
2008".  I considered storing the "February 2008" date as February 1, 2008,
but this is an inaccurate record of the published date.

I've considered storing the date as three separate numeric fields:
"month", "day", and "year" and just having day be null (or zero) when it is
unknown.  For searching, I figured that I could have a computed field that
would build the date in the form "YYYYMMDD" that would allow me to still
sort sequentially (if DD [day]) is null it we be represented as zero (such
as 20080200) or if it is known it would be the actual value, of course
(20080215).

This also presents some minor user interface issues since I won't be able
to use standard date entry components.

Is my approach the best way to handle this situation?  Any suggestions
would be appreciated. >>

I would use 3 integer columns, one for each date component, and then use a
computed or generated column to implement the "massaging" back to a proper
date column that can be indexed, etc.

--
Tim Young
Elevate Software
www.elevatesoft.com

Two Fields:  TheDateField and DateFlagField

I would store all Month/Year  dates with Day = 1 and have a DateFlagField =
dfYearOnly, df YearMonth, dfYearMonthDay,... etc as needed

dfYearOnly would be Jan/01 + Year

That way you get the benefit of querying the datafield and it's precision if
and as required.

Walter


"Greg Bishop" <bishop@porpoisemedia.com> wrote in message
news:C47D1228-FD4B-40F3-AE22-326A89AD56D3@news.elevatesoft.com...
>I have a situtation where I need to record dates of reports.  Usually the
>dates on these documents include the standard month, day, and year (such
> as February 15, 2008).  However, sometimes they are published as just
> "February 2008".  I considered storing the "February 2008" date as
> February
> 1, 2008, but this is an inaccurate record of the published date.
>
> I've considered storing the date as three separate numeric fields:
> "month", "day", and "year" and just having day be null (or zero) when it
> is
> unknown.  For searching, I figured that I could have a computed field that
> would build the date in the form "YYYYMMDD" that would allow me to still
> sort sequentially (if DD [day]) is null it we be represented as zero (such
> as 20080200) or if it is known it would be the actual value, of course
> (20080215).
>
> This also presents some minor user interface issues since I won't be able
> to use standard date entry components.
>
> Is my approach the best way to handle this situation?  Any suggestions
> would be appreciated.
>
> Greg
>