Is there a native firebird function that formats the date (date)?
As in Mysql we have the DATE_FORMAT () function would have something similar in firebird?
Is there a native firebird function that formats the date (date)?
As in Mysql we have the DATE_FORMAT () function would have something similar in firebird?
Firebird does not have a function ready to format date. I believe you can create a procedure that returns a formatted date. There is a SELECT to return the date in dd / MM / yyyy format.
SELECT CAST(EXTRACT(DAY FROM Campo) || '/' || EXTRACT(MONTH FROM
Campo) || '/' || EXTRACT(YEAR FROM Campo) AS VARCHAR) AS DIA_MES_ANO FROM Tabela;
Procedure that passes an integer value that returns the date according to a country, in this case Brazil equals 1 and US equals 2 for the variable res declared as return.
create procedure formatdate(locale int) returns (res date)
begin
// Formato brasileiro
IF (locale = 1) THEN BEGIN
SELECT CAST(EXTRACT(DAY FROM Campo) || '/' || EXTRACT(MONTH FROM
Campo) || '/' || EXTRACT(YEAR FROM Campo) AS VARCHAR) AS DIA_MES_ANO FROM Tabela INTO res;
END;
// Formato americano
IF (locale = 2) THEN BEGIN
SELECT CAST(EXTRACT(MONTH FROM Campo) || '/' || EXTRACT(DAY FROM
Campo) || '/' || EXTRACT(YEAR FROM Campo) AS VARCHAR) AS DIA_MES_ANO FROM Tabela INTO res;
END;
end;
I use a proc for this.
create or alter procedure SP_FORMATA_DATA (
DATA DATA)
returns (
RESULTADO varchar(10))
as
BEGIN
-- Sem data?
IF(DATA IS NULL)THEN
-- Sem resultado tambem.
RESULTADO = NULL;
ELSE
-- Converte a data p/ texto no formato brasileiro.
RESULTADO = (LPAD(EXTRACT(DAY FROM DATA), 2, '0') || '/' ||
LPAD(EXTRACT(MONTH FROM DATA), 2, '0') || '/' ||
LPAD(EXTRACT(YEAR FROM DATA), 4, '0'));
-- Retorna o resultado.
SUSPEND;
END
And to call the proc:
SELECT * FROM SP_FORMATA_DATA('2014.04.14')
Here I use Visual Studio 2015 and I use it in query routines by dates
where (SUBSTRING (DST001.DATA_FAT FROM 7 FOR 4) || SUBSTRING (DST001.DATA_FAT FROM 4 FOR 2) || SUBSTRING (DST001.DATA_FAT FROM 1 FOR 2)) BETWEEN '' & ANOMESDIA_INI & And '"& ANOMESDIA_FIM &" "" &
Try this In my DB I store the dates as varchar (10) - DD / MM / YYYY I get the dates of a datetimepicker and strings the strings anomesdia. ANOMESDIA_INI as string="20170919" ANOMESDIA_FIM as string="20170922"