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)