|Subject||RE: [firebird-support] Extract Date|
|Author||Svein Erling Tysvær|
>>Perhaps it is a date/time format issue in IB Manager. Is the shown timeI think Thomas wondered whether it was a display issue, i.e. that Firebird returned a date, but that IB Manager still showed it as a DateTime and added zeroes to the date. I'm pretty sure that Thomas (like me) use Database Workbench, and I wouldn't be surprised if he didn't know much about IB Manager. So, how does IB Manager normally display dates (e.g. fields created as DATE in a Firebird 2.1 database)?
>I don't quite understand what u mean.
Another thing that probably could explain that CAST ... AS DATE ... doesn't work, is if you use a DIALECT 1 database (that would normally indicate that it is an old database originally created in InterBase 6 or before) - I at least think that DATE in Dialect 1 is the same as DATETIME in Dialect 3. I don't think Dialect 1 has any support for date only.
Other than this, well, if your question is for display purposes only, you could of course try
cast(MyDateField as Char(10))
or something like
cast(case when extract(day from MyDateField) < 10 then
'0' || cast (extract(day from MyDateField) as char(1)
cast (extract(day from MyDateField) as char(2)
end || '.' ||
cast(case when extract(month from MyDateField) < 10 then
'0' || cast (extract(month from MyDateField) as char(1)
cast (extract(month from MyDateField) as char(2)
end || '.' ||
cast (extract(year from MyDateField) as char(4)
if you want a different format.