Issues with imported dates

Known Issues:

  • Dates with no year (1,702 observations).  Since dates require years for these an arbitrary year of 1840 was assigned.
  • Dates with no day were assigned to the 15th of the month
  • Dates with no month and day were assigned to June 15th (I believe, Ben will have to confirm this)

Quirks I can't explain:

  • 7 records (nid >530810 And nid <530817) converted 6/30/1993 to 6/30/1669 fixed 7/11/13


There are no future dates in the database, and I'm not seeing the date shift described.  Perhaps SQL Server doesn't know what to make of the date formats we are exporting and that is where the shift is happening on import?  Perhaps we need to tweak the date export format so SQL Server can parse?

    Query7 ObserveDetailID

Priority: -> high
State: -> active
Client: -> Observations
Assigned: unassigned -> idfg-bthomas



ObserveDetailID Nid
161864 581995
161844 581977
214771 597601
213679 596546



The problemmatic dates in the

State: active -> pending

The problemmatic dates in the csv export were the result of a date formatter outputting 7/1/40 rather than 7/1/1840.  I did this to save screen real estate on

As a result, MS Excel, made assumptions about what the "40" stood for and chose the nearest date.  1940 for 40 and for 20, 2020.  The date stored in the database was never incorrect.

I have updated the date formatter from "n/j/y" to "n/j/Y" (see  It will now output the 4-digit year.

Ok, I did some research into

Ok, I did some research into the partial date add-on module for dates.  It works.  And we don't want to go there.

Doing so requires discarding much of what makes dates have value.  Stuff like validation.  And sorting.  So let's stick with dates but find a clean way to use them.


Decisions needed:

  • What should we do about dates with no year?
  • What should we do about dates with no month or day?
  • What should we do about dates with no day?


Either respond in the comments or should we meet IRL and hash this out?