Sorry, I can't visualize what you are suggesting. I will do the following, because I think I should store the date as a single column after all, however, I must store a value for the year:
Year: 1 == "Not known" (implicit dummy value). If the user doesn't supply a year, 1 is used.
var
birthday: TDate;
year: Integer;
// ...
if CbHasYear.Checked then
year := EdYear.Value
else
year := 1;
birthday := EncodeDate(year, CbMonth.ItemIndex + 1, EdDay.Value);
// .... SQLQuery.SQL.Text uses placeholder ':birthday', which is stored as TEXT in SQLite3 database ('YYYY-mm-dd')
SQLQuery1.Params.ParamByName('birthday').AsString := FormatDateTime('YYYY-mm-dd', birthday); // using FormatDateTime with explicit format guarantees the format always matches sqlite3
To check who has their birthday today, you can do:
// THIS DOES NOT WORK FOR FEB 29 !!! Don't use !!!
'SELECT * FROM person WHERE strftime(''%d-%m'', ''now'')=strftime(''%d-%m'', birthday);'
So I tried to figure out a proper query that works in SQLite3, but also the method of computing age and age in nDays by dividing by 365.25 etc. there are rounding errors and it never worked properly for all possible configurations. So in this particular case, I am going to select all entries and check them in Pascal Code:
var
nDays: Integer;
nRemainingDays: Integer;
dayOfYearToday: Integer;
dayOfYearBirthday: Integer;
begin
nDays := EdNDays.Value;
dayOfYearToday := DayOfTheYear(Now);
SQLQuery1.SQL.Text := 'SELECT * FROM person;'; // optionally add check for NULL or '', if your config allows it
SQLQuery1.Open;
SQLQuery1.First;
while not SQLQuery1.EOF do
begin
ShowMessage(SQLQuery1['first_name']);
dayOfYearBirthday := DayOfTheYear(StrToDate(SQLQuery1['birthday']));
nRemainingDays := dayOfYearBirthday - dayOfYearToday;
if nRemainingDays < 0 then // if birthday already happened this year, we adjust the number of days
nRemainingDays += DaysInYear(Now); // if Now is a leap year, it will be automatically correctly calculated, as opposed to += 365
if nRemainingDays <= nDays then
ShowMessage('Birthday is coming up');
SQLQuery1.Next;
end;
end;
I know this might not be optimal but the way I see it, SQLite3 would have to look at every entry as well, so...
I claim this works for any combination of currentDate/birthday/upcoming in N days and it's clean (except for the fact I'm doing the selection in Pascal code maybe).
Obviously you would not mix the query-looping code with the computation code, but I kept it all together for demonstration.
Since SQLite3 expects "YYYY-mm-dd", and your locale may change the date separator in Pascal, I added the following as well (Pascal defaults to "dd-mm-YYYY"):
DefaultSQLFormatSettings.DateSeparator := '-';
DefaultFormatSettings.DateSeparator := '-';
DefaultSQLFormatSettings.ShortDateFormat := 'y/m/d';
DefaultFormatSettings.ShortDateFormat := 'y/m/d';
I was too lazy to test which of the two you actually need to set, lol.
Another option would be storing the JulianDay as REAL, but I like the readability of using TEXT in SQLite3 for dates.
For completeness, here would be a possible implementation as standalone function (you could name it something more generic, like 'DaysUntilAnnualDay' or 'DaysUntilDayOfAYear', since it will compute the amount of days until any dayOfAYear). Additionally, one might want to pass the reference date as an argument (currently hard-coded as current date).
function DaysUntilBirthday(const birthDate: TDate): Integer;
begin
result := DayOfTheYear(birthDate) - DayOfTheYear(Now);
// Add correction if birthday already was in this year:
if result < 0 then
result += DaysInYear(Now);
end;
Update: I figured out how to implement the same logic in a SQLite3 query:
SELECT
*,
strftime('%j', birthday) - strftime('%j', 'now') AS days_remaining
FROM
person
WHERE :n_days >= CASE
WHEN days_remaining >= 0 THEN days_remaining
ELSE days_remaining + strftime('%j', strftime('%Y-12-31', 'now'))
END
;
So I guess now one could decide which way to do it performs better... I have no idea.
Update 2:
I now realized that StrToDate can accept "one to three" numbers separated by date separator, so it will do both 'YYYY-mm-dd' or 'mm-dd', thus I could also just store either 'YYYY-mm-dd' or 'mm-dd' inside the database. However, I'm not sure what sort of problems that might create down the line.