Subject Re: [firebird-support] There is a way to format datetime directly in SQL ?
Author Huan Ruan

Hi everybody,
> There is a way to format a datetime inside a query ?
> When use a CAST function to convert datetime to string :
> CAST(SUBSTRING(CAST(a.LAST_UPDATE as varchar(30)) FROM 1 FOR 10) <-
> Get only date, without a time.
> From this cast, returning date in format YYYY-MM-DD, but I would like
> to see DD-MM-YYYY for concatenate purpose.
> Of curse that I can use SUBSTRING to get day, month and year
> separately, but its too long to be written.

There is no function in Firebird to directly format a date. However, there
is a better way in SQL to get each component out of a date (e.g. day,
month, year, etc) than SUBSTRING. It's EXTRACT. e.g.

extract(year from current_date) || '-' ||
lpad(extract(month from current_date), 2, '0') || '-' ||
extract(day from current_date)
from rdb$database

Note, the 'lpad' is only needed if you want to display May as '05' instead
of '5'.



[Non-text portions of this message have been removed]