Skip to content
Advertisement

How can I filter records in an SQLite database against a DateTime value that was not supplied?

I have an SQLite database of TimeRecords. When a new record is created, no EndTime is provided. I want to retrieve the first record where the EndTime was not supplied and assign it to a new TimeRecord

EndTime is stored as a DateTime variable.

My Current Query

public static Models.TimeRecord GetMerchandiserCurrentTimeRecord(Models.Merchandiser merchandiser)
{
    lock (collisionLock)
    {
        return database.Table<Models.TimeRecord>().FirstOrDefault(timeRecord => timeRecord.MerchandiserId == merchandiser.Id && timeRecord.EndTime == null);
    }
}

Calling the Query

Models.TimeRecord CurrentTimeRecord { get; set; }

// I assign the CurrenTimeRecord in the Constructor
CurrentTimeRecord = Database.TimeRecordDatabase.GetMerchandiserCurrentTimeRecord(SelectedMerchandiser);

I have done some research and discovered that a DateTime does not store Null values, but instead defaults to the 1st January 0001 00:00:00.000.

Therefore, I can understand why my above query was not returning any result, so I changed the query to the below, but still I am not retrieving any result.

return database.Table<Models.TimeRecord>().FirstOrDefault(timeRecord => timeRecord.MerchandiserId == merchandiser.Id && timeRecord.EndTime == '01/01/0001');

Advertisement

Answer

I managed to solve this. Instead of checking against null or a string value I checked against a new instance of DateTime

Refer to below code

DateTime NoSuppliedDate = new DateTime();
return database.Table<Models.TimeRecord>().FirstOrDefault(timeRecord => timeRecord.MerchandiserId == merchandiser.Id && timeRecord.EndTime == NoSuppliedDate);

Edit: A better way was to use DateTime.MinValue as commented by @sqmoore.

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement