Simple select fails in Firebird

Question

All I want to do is select records between two dates to second precision (between YYYY-MM-DD HH:II:SS and YYYY-MM-DD HH:II:SS). The field which contains date AND time is in TIMESTAMP format. I can't even select records by date.

The query

SELECT *
FROM INVOICE
WHERE CAST(INVOICE.WHENINVOICED AS DATE) = CAST('2010-10-15' AS DATE)
ORDER BY INVOICE.WHENINVOICED ASC

returns no records and no error. Records exist with this date.

Solution

SELECT * FROM INVOICE 
WHERE WHENINVOICED 
BETWEEN '2010-01-01' AND  '2010-10-15'
ORDER BY WHENINVOICED ASC

You do not need to cast types. WhenInvoiced is a date type column ofcourse?

Comments

[Hemaulo]

Umm, WHENINVOICED is a timestamp, as I wrote in my post. Full definition is WHENINVOICED Timestamp(D_DATE). If I view table data in FlameRobin, I can see that there is stored date and time.

[JustMe]

@Hemaulo It will still works, but it will get default time as: "00:00:00", so You can just add it to date

[Hemaulo]

Big thanks, I got the idea. The problem was that I didn't specified time and therefore select was assumed from 2010-10-15 00:00:00 - 2010-10-15 00:00:00. No wonder why records weren't returned.

[JustMe]

@Hemaulo And if you do that then: cast('2010-05-05 22:22' as timestamp)