Why can't I extract date or time from timestamp?

It's because the SQL standard says so. However, you can use CAST instead:


SELECT cast(hire_date as date), cast(hire_date as time)
FROM EMPLOYEE


If this fails from some reason, you can always extract day, month and year and use string concatenation to create a date field. If you want to extract date as string, you would use something like this:

SELECT hire_date,
extract(day from hire_date)||
'.'|| extract(month from hire_date)||
'.'|| extract(year from hire_date)
FROM EMPLOYEE


Of course, that gets you the d.m.y format used in many european countries. You can combine parts differently to get the needed format. For example, to get the m/d/y format used in US, you would use something like:


SELECT hire_date, extract(month from hire_date)||
'/'|| extract(day from hire_date)||
'/'|| extract(year from hire_date)
FROM EMPLOYEE


To construct a date field from string, you can use any of the following formats supported by Firebird: d.m.y, m/d/y or y-m-d. Firebird detects the format by the separator that is used: dot, slash or dash. In short, the follwoing CASTs create the same date field:


cast( extract(day from hire_date)||
'.'|| extract(month from hire_date)||
'.'|| extract(year from hire_date) as date)

cast( extract(month from hire_date)||
'/'|| extract(day from hire_date)||
'/'|| extract(year from hire_date) as date)

cast( extract(year from hire_date)||
'-'|| extract(month from hire_date)||
'-'|| extract(day from hire_date) as date)