How to compare two TimeStamp values

[vishualsoft]

Hi,

I have one small doubt, I am using firebird as a database and i have one field named as "Date_Time" of type timestamp
(it looks like '01.01.2009, 08:00:02.968' in the database).

Here i want to compare two records in the field "Date_Time", how do i know which record's datetime is greater than another record's date and time i.e. which timestamp is grater than another one.

Thanks in advance.


Vishal

[David B]

I am uncertain but will try and point you in the right direction

try

SELECT * FROM YourTable WHERE cast(YourTimeField1 as time) > cast(YourTimeField2 as time)

I am uncertain if this works but I am pretty sure you have to use the CAST command.

Other people here may have a better solution

[vishualsoft]

I am uncertain but will try and point you in the right direction

try

SELECT * FROM YourTable WHERE cast(YourTimeField1 as time) > cast(YourTimeField2 as time)

I am uncertain if this works but I am pretty sure you have to use the CAST command.

Other people here may have a better solution


Thanks, Let me explain the problem in detail:

Actually, the value seems in the database as '31/12/2009 19:06:57.000', but when i try to get through a query component like:

Query1.FieldByName('Date_Time').AsString

then it gives the value in a string variable "sDatabaseTimeStamp" as below:

sDatabaseTimeStamp -> '31/12/2009 7:06:57'

But actually, i want to compare the value '31/12/2009 19:06:57' (milioseconds are not required for comparision) with another one like

'08/01/2010 09:24:18' (i.e. morning 09:24:18)

In short, i would like to ask how do i compare these two values like

'31/12/2009 19:06:57' with '08/01/2010 09:24:18' i.e. 8th Jan 2010(milisecond part is not required for the comparision)

Time part is stored with the date in 24 hours format

Thanks in advance

Vishal

[vishualsoft]

Thanks, Let me explain the problem in detail:

Actually, the value seems in the database as '31/12/2009 19:06:57.000', but when i try to get through a query component like:

Query1.FieldByName('Date_Time').AsString

then it gives the value in a string variable "sDatabaseTimeStamp" as below:

sDatabaseTimeStamp -> '31/12/2009 7:06:57'

But actually, i want to compare the value '31/12/2009 19:06:57' (milioseconds are not required for comparision) with another one like

'08/01/2010 09:24:18' (i.e. morning 09:24:18)

In short, i would like to ask how do i compare these two values like

'31/12/2009 19:06:57' with '08/01/2010 09:24:18' i.e. 8th Jan 2010(milisecond part is not required for the comparision)

Time part is stored with the date in 24 hours format

Thanks in advance

Vishal


Hi,

I got the solution for my above problem, so i would like to share with you.

Code is as follows:

var
cmMiliSecs1, cmMiliSecs2 : Comp;
dtDate2, dtTime2 : TDateTime;
dtDateTime1, dtDateTime2 : TDateTime;
begin
//Following line will contain the database value i.e. Timestamp value
dtDateTime1 := Took the timestamp value i.e. DateTime value from the database;

cmMiliSecs1 := TimeStampToMSecs(DateTimeToTimeStamp(dtDateTime1)) ;

dtDate2 := Took the required date value;
dtTime2 := Took the required time value;

dtDateTime2 := dtDate2 + dtTime2;

cmMiliSecs2 := TimeStampToMSecs(DateTimeToTimeStamp(dtDateTime2)) ;

//comparing values
if cmMiliSecs2 > cmMiliSecs1 then
ShowMessage('cmMiliSecs2 is greater than cmMiliSecs1)
else
ShowMessage('cmMiliSecs1 is greater than cmMiliSecs2);

end;

this gives me exact result which i needed.


Vishal