sql - Select Varchar as Date

Question

I want to select a varchar field as a date field

For example a field has this value "30.12.2011 21:15:03"

and when i select this

select DATE from TABLE where DATE = '30.12.2011'

i get no result.

Answer 1

You ask about getting the date part of a timestamp field, but what your question is actually about is filtering on the date of a timestamp field. There is a much simpler method of accomplishing that: you can use the knowledge that all the possible timestamps on a specific date won't have any timestamps for different dates between them.

select DATE
from TABLE
where DATE >= '30.12.2011' and DATE < '31.12.2011'

Your edit explains that you haven't got a timestamp field at all. Nevertheless, a similar approach may still work:

select DATE
from TABLE
where DATE LIKE '30.12.2011 %'

Or the Firebird-specific

select DATE
from TABLE
where DATE starting with '30.12.2011 '