Problems with DATEs
Posted: Fri 14 Jul 2017 03:06
Howdy Maxim!
I am trying to get an understand with TDate fields and I am getting very inconsistent results.
I have created two tables with a date field called EventDate and a time field called DepartTime.
One table is a TkbmMemTable and the other is a TVirtualTable.
I use the routine below to populate the two tables:
for (TDateTime nEventDate = TDateTime(2017,1,1); nEventDate <= TDateTime(2017,12,31); nEventDate = IncDay(nEventDate,1))
{
for (TDateTime nDepartTime = EncodeTime(12,0,0,0); nDepartTime <= EncodeTime(18,0,0,0); nDepartTime = IncHour(nDepartTime,2))
{
TestTable_->Insert();
TestTable_EventDate->Value = nEventDate;
TestTable_DepartTime->Value = nDepartTime;
TestTable_->Post();
TestVirtualTable_->Insert();
TestVirtualTable_EventDate->Value = nEventDate;
TestVirtualTable_DepartTime->Value = nDepartTime;
TestVirtualTable_->Post();
}
}
Each table has 1460 records (365 days / 4 hours per day).
If I execute the following statement against the TkbmMemTable:
DELETE FROM Test WHERE EventDate IN (DATE('2017-06-01'))
4 records are deleted as expected.
If I run the the following against the TkbmMemTable:
DELETE FROM Test WHERE EventDate NOT IN (DATE('2017-06-01'))
ALL records are deleted including the 4 that should NOT be deleted.
If I run either statement against the TVirtualTable, NO RECORDS ARE DELETED.
How do I get consistent date (and time) conditions? My program is a reservation system and dates / times are used extensively.
Well, I will hope for an answer when I wake up in the morning.
Thanks,
Aggie85
I am trying to get an understand with TDate fields and I am getting very inconsistent results.
I have created two tables with a date field called EventDate and a time field called DepartTime.
One table is a TkbmMemTable and the other is a TVirtualTable.
I use the routine below to populate the two tables:
for (TDateTime nEventDate = TDateTime(2017,1,1); nEventDate <= TDateTime(2017,12,31); nEventDate = IncDay(nEventDate,1))
{
for (TDateTime nDepartTime = EncodeTime(12,0,0,0); nDepartTime <= EncodeTime(18,0,0,0); nDepartTime = IncHour(nDepartTime,2))
{
TestTable_->Insert();
TestTable_EventDate->Value = nEventDate;
TestTable_DepartTime->Value = nDepartTime;
TestTable_->Post();
TestVirtualTable_->Insert();
TestVirtualTable_EventDate->Value = nEventDate;
TestVirtualTable_DepartTime->Value = nDepartTime;
TestVirtualTable_->Post();
}
}
Each table has 1460 records (365 days / 4 hours per day).
If I execute the following statement against the TkbmMemTable:
DELETE FROM Test WHERE EventDate IN (DATE('2017-06-01'))
4 records are deleted as expected.
If I run the the following against the TkbmMemTable:
DELETE FROM Test WHERE EventDate NOT IN (DATE('2017-06-01'))
ALL records are deleted including the 4 that should NOT be deleted.
If I run either statement against the TVirtualTable, NO RECORDS ARE DELETED.
How do I get consistent date (and time) conditions? My program is a reservation system and dates / times are used extensively.
Well, I will hope for an answer when I wake up in the morning.
Thanks,
Aggie85