How do convert or display the date or time as string?

Simply use CAST to appropriate CHAR or VARCHAR data type (big enough). Example:

CREATE TABLE t1 ( t time, d date, ts timestamp );
INSERT INTO t1 (t,d,ts) VALUES ('14:59:23', '2007-12-31', '2007-12-31 14:59');

SELECT CAST(t as varchar(13)), CAST(d as varchar(10)), CAST(ts as varchar(24))
FROM t1;

Firebird would output times in HH:MM:SS.mmmm format (hours, minutes, seconds, milliseconds), and dates in YYYY-MM-DD (year, month, day) format.

If you wish a different formatting you can either use SUBSTRING to extract the info from char column, or use EXTRACT to buld a different string:

SELECT extract(day from d)||'.'||extract(month from d)||'.'||extract(year from d)
FROM t1;

You might want to pad numbers with zero, to get 01.01.2000 instead of 1.1.2000. A common way to do this is to use CASE (available since Firebird 1.5) to prefix a number with zero when it's a single digit. Alternatively, you can use this nice trick with SUBSTRING (available in all Firebird versions):

SELECT hire_date,
substring(100+extract(day from hire_date) from 2 for 2)||'.'||
substring(100+extract(month from hire_date) from 2 for 2)||'.'||
extract(year from hire_date)
FROM employee

If you need some complex formatting and don't want to bother with extracts, substrings, etc. you can use DateToStr function from open source UDF libraries like rFunc or FreeAdHoc:

FreeAdHoc UDF library is actively developed and includes most of rFunc functions with compatible names and arguments.

Do you find this FAQ incorrect or incomplete? Please e-mail us what needs to be changed. To ensure quality, each change is checked by our editors (and often tested on live Firebird databases), before it enters the main FAQ database. If you desire so, the changes will be credited to your name. To learn more, visit our add content page.

All contents are copyright © 2007-2023 unless otherwise stated in the text.

Links   Firebird   News   FlameRobin   Powered by FB: Home Inventory   Euchre  
Add content   About  

 Installation and setup
 Backup and restore
 Connectivity and API
 Errors and error codes