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

select DATE from TABLE where cast(DATE as date) = '30.12.2011'

Date field is a timestamp

Answer 2

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 '

Answer 3

Assuming the field is a date field, use the DATE introducer combined with yyyy-mm-dd (or TIMESTAMP with time as well).

So use:

select datefield from sometable where datefield = DATE '2011-12-30'

Technically you can leave off the introducer, but it is 'correcter' in the light of the SQL standard.

Assuming a TIMESTAMP field, you won't get results unless the timestamp is (always) at 00:00:00.0000 (in which case it should have been a DATE instead).

For the comparison to work, you need to use either BETWEEN, eg:

select timestampfield from sometable 
where timestampfield BETWEEN '2011-12-30 00:00:00.0000' AND '2011-12-30 23:59:59.9999'

or truncate the timestamp to a date (this may adversely effect performance if the timestamp is indexed, because then the index can no longer be used), eg:

select timestampfield from sometable
where CAST(timestampfield AS DATE) = '2011-12-30'

If the date is stored in a VARCHAR field (which in itself is a bad idea), there are several solutions, first is to handle it as date manipulation:

select varcharfield from sometable
where CAST(CAST(varcharfield AS TIMESTAMP) AS DATE) = '2011-12-30'

The double cast is required if you have a time-component in VARCHARFIELD as well. This assumes dates in the supported format listed below. If you use BETWEEN as above, you can use a single cast to timestamp)

The other solution (as suggested by hvd) is to treat it purely as string manipulation, for example:

select varcharfield from sometable
where varcharfield STARTING WITH '30.12.2011'

This has its own set of problems if you want to select ranges. Bottomline: use a real TIMESTAMP field!

Note that Firebird supports multiple formats:

• yyyy-mm-dd, eg 2014-05-25 (ISO-8601 format, probably best to use as it reduces confusion)
• dd.mm.yyyy, eg 25.05.2014
• mm/dd/yyyy, eg 05/25/2014
• mm-dd-yyyy, eg 05-25-2014
• dd mmm yyyy, eg 25 MAY 2014 (+ variations with a -, . or / as separator)
• mmm dd yyyy, eg MAY 25 2014 (+ variations with a -, . or / as separator)

Answer 4

Here is the answere to my question:

CAST
(
SUBSTRING
(field FROM 1 FOR 2)
||'.'||
SUBSTRING
(field FROM 4 FOR 2)
||'.'||
SUBSTRING
(field FFROM 7 FOR 4)
AS DATE)

This took me 5 hours to find this out, maybe there should be a "-" instead of "." but it works.