I'm trying to map out some of my family tree but I've stumbled upon a problem of not being able to put in early dates.

I created the shapefile in QGIS and included a date field (dd-mm-yyyy). Whenever I enter in a date earlier than 1753 It will not let me (i.e. it wont let me put a 6 next to the 1 for 1600) it then defaults back to todays date.

How do I get around this??


Credit to Joseph for finding this post: What is the significance of 1/1/1753 in SQL Server?, which I summarize here:

When Britain switched from the Julian calendar to the Gregorian calendar, the dates from September 3, 1752 to September 13, 1752 were lost. This would cause computational errors (eg how many days between September 1 and September 14 in 1752?). SQL avoids these sort of errors by forbidding dates before 1753.

So, basically you can't put in any dates before 1753 into a date-time formatted field. As a workaround, you can use a different field format. Here are several options:

  • Text - store the entire field as a text string.

    Note: it's important to use consistent formatting, EG August 15, 1600 vs 1600_08_15 vs 8-15-1600 vs 15/8/1600. Using a format like YYYY-MM-DD or YYYY_MM_DD will allow you to easily sort by date.

    Pro: easy for a person to read; you can use the to_date() function in expressions for dates after 1753

    Con: difficult for the computer to read; the to_date() function doesn't work on dates before 1753; typos can be a problem

  • One Integer Field - store the date as a single number, eg YYYYMMDD

    Pro: easy to sort and use in expressions

    Con: slightly difficult for a human to read

  • Separate Integer fields - store each part of the date (year, month, day) in a separate integer field

    Pro: you can sort and categorize by year, month and day; easy to use in expressions

    Con: data entry into 3 separate fields is less efficient; QGIS only lets you sort by one field at a time

  • Decimal - convert the month and day into decimals, and store the date as a single decimal number

    eg, August 15, 1600: (August = 8/12 = 0.667) + (15th = (15/31)/12 = 0.040) + 1600 = 1600.707 Pro: easy to sort by date; easy to use in expressions

    Con: difficult for a person to read; can't easily parse by month, season, etc.

As a variation of these methods, consider using Ordinal Dates.

An ordinal date is a calendar date typically consisting of a year and a day of year ranging between 1 and 366 (starting on January 1), though year may sometimes be omitted. -Wikipedia

  • Text with ordinal dates: YYYY-DDD
  • Integer with ordinal dates: use two integer fields (year, ordinal-day)
  • Decimal with ordinal dates: YYYY.DDD or YYYY.(DDD/365 or DDD/366 for leap years)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.