How vague dates are stored

This is a technical topic which will be of use to more advanced users who wish to query the underlying database from other software tools.

Vague dates are always stored as a range of dates using three fields in the database.

Name

Purpose

Type

Vague_date_start

the starting date of the range

Date

Vague_date_end

the ending date of the range

Date

Vague_date_type

a one or two letter code indicating which of the possible formats was entered

Text

If a table contains more than one vague date, then two or more sets of these three fields will be present, and their names will be prefixed to indicate their purpose (e.g. born_vague_date_start, died_vague_date_start). The following indicates the contents of these three fields for the different types which are supported:

Type

Example

Start_date

End_date

Type

Standard date

23 Mar 1987

23/3/1987

23/3/1987

D

Standard date range

23 Mar 1987 - 30 Mar 1987

23/3/1987

30/3/1987

DD

Month

Mar 1987

1/3/1987

31/3/1987

O

Month range

Mar 1987 - Jun 1987

1/3/1987

30/6/1987

OO

Season

Summer 1987

1/6/1987

31/8/1987

P

Year

1987

1/1/1987

31/12/1987

Y

Year range

1981 - 1987

1/1/1981

31/12/1987

YY

Open ended year range

1987 -

1/1/1987

<empty>

Y-

Up to year

- 1987

<empty>

21/13/1987

-Y

Month only

July

1/7/9999

31/7/9999

M

Season only

Summer

1/6/9999

31/8/9999

S

Unknown

Unknown

<empty>

<empty>

U

However, in the SQL Server database itself, the Vague_date_start and Vague_date_end are stored as integers. To view them as dates in Microsoft Access you will need to use a function such as CDate. For example:

SELECT Sample.Sample_Key, Sample.Vague_Date_Start, CDate([Sample]![Vague_Date_Start]) AS StartDateddmmyy,
Sample.Vague_Date_End, CDate([Sample]![Vague_Date_End]) AS EndDateddmmyy, Sample.Vague_Date_Type, Sample.Entry_Date
FROM Sample
WHERE (((Sample.Entry_Date)>#1/1/2011#))
ORDER BY Sample.Vague_Date_Start;

This query lists the following data for samples entered after 1/1/2011 in ascending Vague_Date_Start order:
Sample_Key, Vague_Date_Start as an integer, Vague_Date_Start in dd/mm/yyyy format,
Vague_Date_End as an integer, Vague_Date_End in dd/mm/yyyy format, Vague_Date_Type and Entry_Date.
In design view the Vague_Date_Start field in dd/mm/yyyy format would be StartDateddmmyy: CDate([Sample]![Vague_Date_Start]).

To view the vague date integers as dates in a SQL Server query you will need to use a built in function such as LCReturnDate. For example, the query above in SQL Server would be:

SELECT Sample.Sample_Key, Sample.Vague_Date_Start, dbo.LCReturnDate(Vague_Date_Start, Vague_Date_Type, 'F') AS StartDateddmmyy, Sample.Vague_Date_End, dbo.LCReturnDate(Vague_Date_End, Vague_Date_Type, 'F') AS EndDateddmmyy, Sample.Vague_Date_Type, Sample.Entry_Date
FROM Sample
WHERE Sample.Entry_Date > '01/01/2011'
ORDER BY Sample.Vague_Date_Start

Note: This query will return different results in an XML report, as including Sample.Vague_Date_Start, Sample.Vague_Date_End and Sample.Vague_Date_Type will return the date as entered so these three fields will return a single column containing vague dates like "15/05/2007 - 20/05/2007", "July 2009 - August 2009", "2011".

Related Topics