Date to String format in Firebird

Question

How can I customize CAST (TimeStamp TO STRING) format in Firebird?

cast (<DateField> as VarChar(25))

It does not work as I want.

I do as I wish with the EXTRACT function

EXTRACT(day FROM <DateField>) || '.' || EXTRACT(month FROM <DateField>)||
'.' || EXTRACT(year FROM <DateField>) || ' ' || EXTRACT(hour FROM <DateField>)

Is there a more practical way, such as MS-SQL Server:

convert(nvarchar(MAX), @now, 108)

Answer 1

I had to do this same procedure for a project today. I needed to perform a LIKE comparison on the date portion of a TIMESTAMP column using a user's input (formatted as dd/mm/yyyy) which could have been only partially inputted:

12   # any date with 12th day, December, or the year 2012
25   # 25th day of any month, or any date in 2025
12/  # 12th day of any month, or any date in December of any year
/12/ # any date in December of any year

This was to mirror the behavior of a Datatables text column filter while exporting the identical data to csv file.

Ugh, what a sucky task. I have never needed to use LIKE on a date column in my professional career and I always advise others against it. Life is not without compromises.

These were my findings: (I'll omit the extremely grotesque OVERLAP() expression that I trialled)

MY_STAMP
// 13.06.2019, 09:25:28.813

-- CAST(MY_STAMP AS VARCHAR(23))
// error: numeric overflow

CAST(MY_STAMP AS VARCHAR(24))
// 2019-06-13 09:25:28.8130

SUBSTRING(CAST(MY_STAMP AS VARCHAR(24)) FROM 9 FOR 2) || '/' || SUBSTRING(CAST(MY_STAMP AS VARCHAR(24)) FROM 6 FOR 2) || '/' || SUBSTRING(CAST(MY_STAMP AS VARCHAR(24)) FROM 1 FOR 4)
// 13/06/2019

EXTRACT(DAY FROM MY_STAMP) || '/' || EXTRACT(MONTH FROM MY_STAMP) || '/' || EXTRACT(YEAR FROM MY_STAMP)
// 13/6/2019

SUBSTRING(100 + EXTRACT(DAY FROM MY_STAMP) FROM 2 FOR 2) || '/' || SUBSTRING(100 + EXTRACT(MONTH FROM MY_STAMP) FROM 2 FOR 2) || '/' || EXTRACT(YEAR FROM MY_STAMP)
// 13/06/2019

I assume ALL of these techniques will perform VERY poorly. So my overriding advice is to convince the powers-to-be to rethink this action so that it can be avoided entirely.

A solid reference: How do convert or display the date or time as string?

In the end I elected to go with casting to varchar and substring'ing, not because of its length or performance (I didn't benchmark), purely because I felt the 100 + padding syntax was too wacky.

The LPAD() function (v2.5.5) wasn't available for our version -- we are on 2.5 at the moment, so we must be just under.

Of course, I have deviated in the delimiting character from the OP's question -- just replace my / with ..