Date_format and AM/PM date format

[Mark Newnham]

Is there any way of presenting times in AM/PM format using date_format, extract etc?

[Mark Rotteveel]

On 26-03-2021 01:53, Mark Newnham wrote:
> Is there any way of presenting times in AM/PM format using date_format,
> extract etc?

No, there is nothing like that built-in. See also
http://tracker.firebirdsql.org/browse/CORE-6507

The simplest way is to solve this in your application, and not try to
format in your SQL query. In Firebird 3 you could also create a UDR that
does the formatting for you.

For example using FB/Java, you can create a UDR Like this:

```
package nl.lawinegevaar.fbjava.experiment;

import java.sql.Timestamp;
import java.text.SimpleDateFormat;

public class FormatDatetime {

public static String formatTimestamp(
Timestamp timestamp, String formatString) {
return new SimpleDateFormat(formatString).format(timestamp);
}

}
```

And - after deploying the jar in your database - define a function with:

```
create function format_timestamp(timestampval timestamp, formatstring
varchar(100))
returns varchar(100)
external name
'nl.lawinegevaar.fbjava.experiment.FormatDatetime.formatTimestamp(java.sql.Timestamp,
String)'
engine java;
```

And then execute:

```
select format_timestamp(localtimestamp, 'yyyy-MM-dd hh:mm a')
from rdb$database
```

Result: 2021-03-26 09:35 AM

The format string follows the syntax of Java's SimpleDateFormat[1]

[1]:
https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/text/SimpleDateFormat.html

--
Mark Rotteveel