I'm not sure how you created your date/time string in your database since that information wasn't given in the question. However, according to the documentation for the
datetime() function, the format you have of
MM/DD/YYYY HH:MM:SS xx is not one of the accepted formats. In fact, if you do
SELECT datetime('1/23/2018 01:40:00 PM') at an SQLite prompt, you get NULL. Whereas, if you use an acceptable format, you do not:
SELECT datetime('2018-01-23') gives
So I think the solution is to write the
ALARMTIME field using
datetime('now'), which does yield a format accepted by
datetime(). If you need to sort based upon the format you have now, you can't do it with
datetime(). You'd need to reformat the field using string functions to get it in a form you could do a string compare with (e.g., as is done here).
The OP has indicated in a comment that the ALARMTIME is set using the following Pascal code:
FieldByName('AlarmTime').AsDateTime := now;
There's no guarantee that Pascal is going to use a date/time string format in this context that is compatible with SQLite's
datetime() function. So Pascal date/time formatting functions can be used to create a format more specifically acceptable by SQLite's
datetime(). Then you'd use something like:
FieldByName('AlarmTime').AsString := FormatDateTime('YYYY-MM-DD hh:nn:ss',now);
Now this will change the default view of the date to
YYYY-MM-DD .... If you still want your view of the table to show
MM/DD/YYYY... then you'll either need to go back to my prior comment about processing the string on the fly in the comparison for sort, or write a little view formatting code so that it displays in a format that's different than what is stored internally, which is a common view/model separation technique.
If you can write your original ALARMTIME format as
MM/DD/YYYY and make sure you pre-pad with zeroes (e.g.,
1/9/2018) then you can use SQLite's
SELECT * FROM Alarms
ORDER BY (substr(ALARMTIME,7,4)||substr(ALARMTIME,1,2)||substr(ALARMTIME,4,2)||substr(ALARMTIME,11)) DESC
And you would create your
ALARMTIME using this:
FieldByName('AlarmTime').AsString := FormatDateTime('dd-mm-yyyy hh:nn:ss', now);
The above solutions are fairly generic. Depending upon the client library you are using (which you have not specified), there may be another more suitable approach to solving the problem.
You cannot do, for example,
SELECT * FROM Alarms ORDER BY ALARMTIME DESC and get an accurate sort since, for example, the date
12/1/2018 would come after
2/1/2018 in that sort ordering even though
2/1/2018 is later in time. This is because
1 in the ASCII collating sequence.
If you need to keep your current
ALARMTIME string format and not change how you're saving it, which is somewhat free form
m/d/yyyy in which the day or month can have one or two digits, you're going to have a bit of work to do in order to sort it if your client library doesn't support some helpers in this regard. Perhaps your only other option would be to use a custom SQLite function. These are written in C and compiled and linked with SQLite. You'd have to find one already written, or write your own.