Storing dates prior than 1601

Thank you, Ian.

I will try this driver.

If you use Julian dates (just floating point numbers) you can avoid issues with storing dates in the database and having limits on minimum dates. Here’s some code to convert back and forth.

Note that the day is a double, so you can get down to hours, minutes and seconds by using fractions of day.

Function JulianFromDate(year as integer, month as integer, day as double) As double
  
  dim y as integer = year
  dim m as integer = month
  dim d as double = day
  if month <= 2 then
    y = y - 1
    m = m + 12
  end if
  dim JulianCalendar as boolean = year < 1582 or (year = 1582 and ((month < 10) or month = 10 and day < 5))
  dim b as integer
  if JulianCalendar then
    b = 0
  else
    dim a as integer = floor(y / 100)
    b = 2 - a + floor(a / 4)
  end if
  return floor(365.25 * (y + 4716)) + floor(30.6001 * (m + 1)) + d + b - 1524.5
End Function


Sub DateFromJulian(jd as double, byref year as integer, byref month as integer, byref day as double)
 
  dim jdate as double = jd + 0.5
  dim Z as integer = floor(jdate)
  dim F as double = frac(jdate)
  dim A as integer
  if Z < 2299161 then
    A = Z
  else
    dim alpha as integer = floor((Z - 1867216.25) / 36524.25)
    A = Z + 1 + alpha - floor(alpha / 4)
  end if
  dim B as integer = A + 1524
  dim C as integer = floor((B - 122.1) / 365.25)
  dim D as integer = floor(365.25 * C)
  dim E as integer = floor((B - D) / 30.6001)
  day = B - D - floor(30.6001 * E) + F
  
  if E < 14 then
    month = E-1
  else
    month = E-13
  end if
 
  if month > 2 then
    year = C - 4716
  else
    year = C - 4715
  end if
End Sub

Function frac(x as double) As double
  dim xx as double = x - floor(x)
  if xx < 0 then
    return xx+1.0
  else
    return xx
  end if
End Function

Hi Paul, thank you for your post.
I think that your solution avoids every date issue, but I have a database and tables which contains date fields, and I cannot change it.
However both SQLite and SQL Server store date fields in the right way: the issue come along when I try to read such records containing date before 1601-01-01: SQLite works fine, so does the MSSQLServer plugin. But SQL Server ODBC driver does not.
However I will study your solution, 'cause I guess it’s interesting.
Thank you.

When you perform your sql select statement your could try casting the date column as a string which means Xojo would retrieve a string which you can manipulate.

It seems a good idea: I’ll try soon your solution.

Thank you a lot!!