How to use TDateTime in SQL Statements?

[bside]

Hi,
I need a SQL statement as this:

SELECT Field1, Field2,... FROM Table1
WHERE (...) and DateTimeField1 + 4 hours > Now

As you can guess this is used to raise an event to control
a process which needs 4 hours to be completed.

My problem is that I do not find the way to
compare the dates/times, they are not numbers inside SQL ...
I've seen you can extract the time using
EXTRACT(Time From DateTimeField1) but what to write in
the right part of the operator?

Thanks in advance,
Dani.

 

[Svanhooft]

hi,

Datetime always a problem. To solve this in a SQL statement you have to use the CAST function. See the delphi helpfile for more information.

Steph

examples

SELECT CAST(SaleDate AS DATE)
FROM ORDERS

SELECT SaleDate,
SUBSTRING(CAST(CAST(SaleDate AS DATE) AS CHAR(10)) FROM 1 FOR 1)
FROM Orders

 

[bside]

Hi,
I've tried this:

SELECT Field1, Field2,... FROM Table1
WHERE CAST(DateTime AS TIMESTAMP)<CAST('1/1/1 0.0.0' AS TIMESTAMP)

I do not receive any mistake, but the compraison returns an incorrect value. It always returns true... Why?

Thanks,
Dani.

 

[LucieLastic]

You may need to use Convert rather than Cast.

Also, the type TimeStamp, I don't think is a datetime but some wierd number to uniquely identify a record.

What database are you using ?

lou

 

[bside]

Ok,
does Convert comand use the same syntax than CAST?.

I'm using paradox standar.

Thanks,
Dani.