extract(week from 'Now') error

Question

I'm running an firebird 2.5 superclassic on my raspberrypi. I execute the following query and it gives the following error:

SELECT foodmanager1.F_US_FIRSTNAME,foodmanager1.F_US_LASTNAME, 
foodmanager1.F_US_PICTURE_URL,
foodmanager2.F_US_FIRSTNAME, foodmanager2.F_US_LASTNAME, 
foodmanager2.F_US_PICTURE_URL,
duty.F_US_FIRSTNAME, duty.F_US_LASTNAME,
duty.F_US_PICTURE_URL,
a.F_FD_DATE from
    T_FOOD_DUTY a
inner join T_USER foodmanager1 on a.F_US_ID1 = foodmanager1.F_US_ID
inner join T_USER foodmanager2 on a.F_US_ID2 = foodmanager2.F_US_ID
inner join T_USER duty on a.F_US_ID3 = duty.F_US_ID
where extract(week from a.F_FD_DATE) = extract(week from 'Now')

With this error:

Specified EXTRACT part does not exist in input datatype

SQL error code=~105.

I know the problem is with the extract(week from 'Now'), because when I manually replace it with a number I do get some results.

Any idea what the problem is, or do you have an alternative?

Answer 1

The problem is that extract accepts any data type, but only works for types date, time or timestamp. In this context, 'Now' is simply a char(3), so extract cannot be used (it can't extract data from a char type).

The confusion stems from the fact that in some contexts (eg assignment or explicit cast to a date, time or timestamp), 'Now' will yield the current date/time. It does not work in this context, because Firebird can't know which of the three types it would need to be; in theory extract accepts any type, what it can actually do is determined by the type it receives.

You need to explicitly coerce it to a date (or timestamp):

1. Using an explicit cast:

   extract(week from cast('Now' as date))
   

2. Using a type-introducer (aka shorthand cast):

   extract(week from date'Now')
   

   Caution: this no longer works in Firebird 4.0 and higher.

3. Or, as suggested in the answer of ain, use the SQL standard 'function' current_date or current_timestamp:

   extract(week from current_date)
   

Answer 2

Use the SQL standard CURRENT_TIMESTAMP (or CURRENT_DATE as the time part is not important when extracting the week) instead of 'NOW'

extract(week from CURRENT_TIMESTAMP)

Or if you really want to use 'NOW' then cast it to date:

extract(week from CAST('Now' AS DATE))